Listing Multiple Finds

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob L - 27 May 2008 13:04 GMT

I have a table of data (12 columns wide) that consists of lists of employees
who have been inducted. I need a way to find all the (say) "Smiths", and
list them (and the data in the other 11 columns).
The formula below seems to be a good start, if I copy it down in the range
that I want the result to appear. The problem is that the list is growing,
and I would like to use a dynamic range name (instead of $A$3:$A$20), but
INDEX seems to spit the dummy with anything but actual addresses. Any help
much appreciated.

=INDEX($A$3:$B$20,SMALL(IF($A$3:$A$20=$A$1,ROW($A$3:$A$20)),ROW(1:1)),2)

Rob Lepper

Reply to this Message

edvwvw - 27 May 2008 13:30 GMT

Rob

You could try a small macro as below: this will create an autofilter

Sub Test()

Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=Range("F1")

End Sub

I have only looked at columns A to C and put the value that you are looking
for in F1- if you attach a button to the macro it will help

edvwvw

>I have a table of data (12 columns wide) that consists of lists of employees
>who have been inducted. I need a way to find all the (say) "Smiths", and
[quoted text clipped - 8 lines]
>
>Rob Lepper

Reply to this Message

Rob L - 28 May 2008 08:06 GMT

Thanks, I'll give it a go and report back...

Rob

> Rob
>
[quoted text clipped - 27 lines]
>>
>>Rob Lepper

Reply to this Message