I need a Lookup of some sort cont.

[Guest]

Ok I tried the auto filter thing, not working the way i need it, here are the
specifics again, I have a chart that looks like this on the first worksheet:

NAME AGE CHILDREN
JOE SMITH 32 YES
DEBBY ALLEN 19 NO
CHRIS JONES 45 YES
TOM WILLIAM 17 YES etc, etc......

OK this list has about 60-80 names on it....now i have another table on
another page that has enough lines for exactly 10 of these names to be
transfered to, I know that there will be only 10 peole from the list who
answer NO to the CHILDREN column, so I wan to be able to somehow pull from
the first list(60 people) and transfer the names of the 10 without kids to
the spot I have on the second page...is this possible, i tried sort/filter,
does not do what i need it to do, can i use a lookup somehow??? PLEASE HELP

 

[David Biddulph]

Select the table of data
Data/ Autofilter
Children/ No
Copy
Paste

 

[Guest]

Ughhhh....lol...I know it can be done this way, but am looking to have it
fill the second table in automatically without having to cut and paste or
copy and paste. The person who will be using this just wants the master list
of names, and then the second table to populate itself without having to
sort/filter cut and paste, there must be a way for this to work, if a macro
is needed, i dont know how to do macros, but can learn, it hought a lookup
could be used, but having problems since one list is 60-80 lines going into a
table that is only 10 lines large. PLEASE HELP

 

[T. Valko]

So, you want just the names? And there will be no more than 10 that meet the
condition? Will there ever be less than 10 that meet the condition?

Assume this data is on Sheet1 starting in cell A2:

JOE SMITH 32 YES
DEBBY ALLEN 19 NO
CHRIS JONES 45 YES
TOM WILLIAM 17 YES

Enter this formula where you want the names listed. Enter this formula as an
array using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

=INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1)))

Copy down 10 rows.

If there may be less than 10 names that meet the condition then use this
version to handle any errors (also arrary entered):

=IF(ROWS($1:1)<=COUNTIF(Sheet1!C$2:C$5,"no"),INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!C$2:C$5="no",ROW(A$2:A$5)-MIN(ROW(A$2:A$5))+1),ROWS($1:1))),"")

Biff

 

[David Biddulph]

I wouldn't have wasted my time answering if I had realised that you had
already asked the question in another thread and got the answer. Please
don't start a new thread in such situations; if you had continued the
previous thread and explained why you weren't happy with the suggestion that
had been made, then you might have stood more chance of getting the answer
you wanted.

 

[Guest]

In Sheet 1
Assuming a header in row 1
Names: A2:A100
Age: B2:B100
Children: C2:C100

In Sheet 2
Header in A1
A2
=IF(ISERR(SMALL(IF(Sheet1!$C$2:$C$100="no",ROW(INDIRECT("1:"&ROWS($A$2:$A$100)))),ROWS($1:1))),"",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$C$2:$C$100="no",ROW(INDIRECT("1:"&ROWS($A$2:$A$100)))),ROWS($1:1))))

Adjust your range to suit
ctrl+shift+enter, not just enter
copy by dragging the Fill handle down as far as needed

 

[Guest]

Thank you so much T. Valko, works like a dream, I swear I dont understand how
people like you and everyone else who helps people on this board learn so
much about Excel, I use it quite often, but i guess im still just at the
basic level...anyway thanks again, and David Biddulph, sorry if i upset you,
i appreciate your time and effort in helping me, did not think anything was
wrong with starting a new thread, did not want people to tell me to use
filter again since i had tried that, anyway sorry man.

One last question, does anyone know how to link a macro to like a cool
picture button to start it, thanks....

and what is considered advanced or expert level Excel, (pivot tables, using
macros, or all these function formulas??? Trying to get better at all this,
just curious as to what is considered the most advanced Excel stuff you can
do???

Thanks again and Happy New Year

 

[Gord Dibben]

You can assign a macro to any object, including a picture.

Insert the picture into your sheet then right-click and "Assign Macro"


Gord Dibben MS Excel MVP

 

[T. Valko]

You're welcome. Thanks for the feedback!

what is considered advanced or expert level Excel, (pivot tables, using
macros, or all these function formulas???

Hmmm.....

That's a hard question to answer since everybody has their own
interpretation of "advanced" and "expert".

????

Biff

 

[pappasd]

I know what you need, it's hard for me to explain, I just happened upon this forum looking to help myself solve another issue... but you need a dynamic index. if you send me the file, I could send it back with the formula.

It will look something like this (brackets to indicate a matrix foluma):
{=index(array, row, column)}

to get the row dynamically:
{=index(array, small(array,k), column)}

which should look like this, referencing the "Children" column:
{=index(array, small(if(array="Yes",row(array)),k), column)}

to make k change dynamically, substitute with column(A:A) if dragging across columns or row(1:1) if dragging across rows...

maybe someone already replied, I don't know...

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com