Conditionally lookup part of small list.

Hi all,

CONDITIONAL LOOKUP

In Sheet1 I have:-

a, a numeric cell in which I manually enter a number (between 4 & 20 (if relevant)).
b, 20 text cells (single column list) of manually entered names.

In Sheet2, I want to display the names from b, above according to the number entered in a, above

e.g.:-
Sheet1
Cell A1 has, for example, '10' manually entered.
Cells B1 thru B20 have 20 distinct names manually entered.
Sheet2 needs to display the first 10 names (top down) from B1 - B20

Can someone please tell me the correct formula/expression to use.

Thanks,
J.
PS: Apologies to Cheeky Charlie for repeating the question. I didn't get a chance to explain my point of confusion in the previous thread.

Try this in B1 of Sheet2:

Please Login or Register  to view this content.

and drag down to B20

Hi rwgrietveld,

Thanks for the reply.

Originally Posted by rwgrietveld

Try this in B1 of Sheet2:

Please Login or Register  to view this content.

and drag down to B20

Can you tell me if there is a way I can change that expression so it can go in an arbitary cell in Sheet2?

It does work as is, but (unless I'm doing something wrong) it has to be in B1, or at least within row1 to fully populate my list.

Or is it me?

Thanks,
J.

Yes the Row() is killing you.

Change it to where you start minus x so it starts from 1.

e.g. you start in Q25 -> Row(Q25)-24

Please Login or Register  to view this content.

Or if you're pedantic like me, using the Q25 example

=IF(ROWS(Q$25:Q25)<=Sheet1!$A$1,Sheet1!B1,"")

The references in ROWS will adapt should you insert/remove rows above etc meaning the formula will itself adapt as a matter of course without need for intervention.