lookup formula

[Guest]

Hi,

I have an issue bringing across data into a table.

In worksheet 1 I have my data, in worksheet 2, I have one table that needs
to be populated by only some of the data in worksheet 1.

The problem is that I have multiple sets of criteria that could meet each
cell in the table. For example, the first column in my table is simply a
name.

But, Not only do i need my table to show how many times the name appears in
worksheet 1 in a different row, but I also need it to seperate out each
individual name.

Example:

In worksheet 1 I may have something like this:


Column 1 Column 2
John Yes
Paul Yes
Stephen No
John Yes
Jason Yes
Paul No
John No
Bob Maybe


I need my table in worksheet 2 to look something like this (the criteria for
returning the value is Yes or Maybe):

Column 1
John
Paul
John
Jason
Bob

Any ideas?

 

[Piscator]

Is this a one time need or will worksheet 2 need to dynamically change
whenever worksheet 1 values change from a maybe or no to a yes?

If it's a one time need then I'd simply have worksheet 2 contain
=IF(Sheet1!B1="yes",Sheet1!A1,IF(Sheet1!B1="maybe",Sheet1!A1,""))
in any rows with names on worksheet1, so
Worksheet1
Fred yes
Bert no
John yes
Paul yes
Sarah yes
John maybe

gives
Worksheet2
Fred

John
Paul
Sarah
John

You can then highlight, copy those cells and paste special values so
you lose the formulae and just get the names listed

 

[Guest]

Try to make a pivot table on a new sheet, you could show only those names
which are marked with Yes or Maybe, and also you could count the name
appearances.

 

[Guest]

hmmm, possibly

Can I do it with out showing any gaps in worksheet 2?

 

[Guest]

=IF(ISERR(SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF((status="yes")+(status="maybe"),ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[Guest]

Ok i would like to try this formula but am getting a bit confused.

Do i replace "status" with the column?

What do i Replace "name" with?

Sorry, i've never used ROW or INDIRECT formulas before so not sure how they
work.

 

[Guest]

"status" is a define name in a range (ex: B2:B100 contain "yes", "no", "maybe")
"names" is a define name in a range (ex: A2:A100 contain a list of names)

 

[Guest]

holy crap it worked.

Thank you so much.

=IF(ISERR(SMALL(IF(('Data Entry'!$B$8:$B$200=Lists!$B$10)+('Data
Entry'!$B$8:$B$200=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data
Entry'!$A$8:$A$200)))),ROWS($1:1))),"",INDEX('Data
Entry'!$A$8:$A$200,SMALL(IF((status=Lists!$B$10)+(status=Lists!$B$11),ROW(INDIRECT("1:"&ROWS('Data Entry'!$A$8:$A$200)))),ROWS($1:1))))

 

[T. Valko]

Are the only possible entries Yes, No or Maybe (and no empty cells)?

If so, you can do this more efficiently (smaller formula, faster
calculating, non-volatile) if you just test for entries that are not "no".

=IF(ROWS($1:1)<=COUNTIF(status,"<>no"),INDEX(name,SMALL(IF(status<>"no",ROW(name)-MIN(ROW(name))+1),ROWS($1:1))),"")

I keep trying to "convince" TM this method is much better (smaller formula,
faster calculating, non-volatile) but "he" doesn't want to listen! <g>

Biff

 

[T. Valko]

I forgot:

That formula is also an array formula entered with CTRL,SHIFT,ENTER (not
just Enter).

Biff