Stepping down a cell when a value is entered

[DubboPete]

Hi all,

I'm trying to put together an 'order of play' page which displays two
teams who should be next to play, depending on whether a cell in Colum
B is blank or filled-in.

For instance cells A11 to A26 contain numbers 1 to 16. Cells C11
contaiin numbers 17 to 32. If cell B11 has an entry of any kind (P
for Playing means the game has commenced, and therefore is not blank)
I would like cell A5 to display the contents of cell A12, and cell C5
to display the contents of cell C12.

If cell B12 then has an entry when the next game commences, I'd like
the static cell A5 to display the contents of cell A13, and static
cell C5 to display the contents of cell C13, and then so on down the
list...

Is there a formula I can use to get this result? I suppose it's like
a rolling formula...

Any help appreciated, and thanks in anticipation!

DubboPete

 

[Mike H]

Hi,

Try this in a5
=IF(COUNTA($B$11:$B$26)>0,LOOKUP(2,1/(B11:B26<>""),A11:A26),"")

and in C5
=IF(COUNTA($B$11:$B$26)>0,LOOKUP(2,1/(B11:B26<>""),C11:C26),"")

Mike

 

[DubboPete]

Hi,

Try this in a5
=IF(COUNTA($B$11:$B$26)>0,LOOKUP(2,1/(B11:B26<>""),A11:A26),"")

and in C5
=IF(COUNTA($B$11:$B$26)>0,LOOKUP(2,1/(B11:B26<>""),C11:C26),"")

Mike











- Show quoted text -

Hi Mike

Thanks for that, almost perfect - but unfortunately it displays the
current game, not the next game. For instance, Game 1 is 1 v 2, when
"P" is entered in B11, then cell A5 displays 1 and cell C5 displays 17
(the current teams playing, not the next teams to play, which should
be 2 vs 18 ...)

Pete