Some kind of lookup needed?

[Guest]

ok here are the details:

I have a three column table on one sheet, one column with name, another with
address, and the last is a yes/no only for kids, looks like this:

NAME ADDRESS KIDS
John Smith 2222 River Rd YES
Bob Gillian 101 Main St NO
Susan Wallow 59 N. Peters YES

This table has about 80 rows of different people. Now what im trying to do
is this, I know for a fact that I will have no more than 10 people who answer
NO to the last column, so i have another table on a different sheet 10 rows
long that i want a list of those with who answer NO put on it. I guess what
im asking is i want Excel to search this 80 row list for those who answer NO
and copy their Name to this other page's table....how can i do this? Thanks
for the help.

 

[Don Guillett]

Why not just use data>filter>autofilter>>>>

 

[Guest]

Because the table that the names get copied onto on the second page is part
of a page that i need printed out with just those who answered NO, so i need
those 10 names separated and copied over to a different table, can autofilter
be used for this purpose? Thank you for your help with this

 

[Dave Peterson]

Maybe you can apply Data|Filter|autofilter, show the rows with No in that
field. Then copy those visible rows to the other sheet???

 

[Don Guillett]

Why don't you try it.

 

[Guest]

well .., one way if you want to get it dynamically extracted in the other
sheet ..

Assuming source data is in Sheet1's cols A to C,
data from row2 down, with key col = col C ("Kids")

In the other sheet,

Put in A2: =IF(Sheet1!C2="No",ROW(),"")
(Leave A1 blank)

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
Copy B2 to E2. Select A2:E2, copy down to cover the max expected extent of
data in Sheet1. Hide away col A. Cols B to E will return the required
results, ie only the lines with "No" under "Kids" col in Sheet1, with all
lines neatly bunched at the top.

---