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

Rob L wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1

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

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

Rob

"edvwvw via OfficeKB.com" <u42512@uwe wrote in message
news:84c6f9e8e661c@uwe...
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

Rob L wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200805/1