List the one nonempty cell

[Guest]

All of column B is empty except one cell. The non-empty cell is in a
different place each week. I want a formula that will give the text in the
nonempty cell, regardless of which cell is nonempty at that point in time.
For example.

Week 4
Cell should say "Text 4"

A B
1
2
3
4 Text 4


Week 2
Cell should say "Text 2"

A B
1
2 Text 2
3
4


Thanks

 

[RichardSchollar]

Hi Adam

Maybe:

=LOOKUP(REPT("z",255),B:B)

Hope this helps!

Richard

 

[T. Valko]

Another one:

=INDEX(B:B,MATCH("*",B:B,0))

Biff

 

[Guest]

works!

thanks

Hi Adam

Maybe:

=LOOKUP(REPT("z",255),B:B)

Hope this helps!

Richard

 

[Guest]

I was wrong, these don't work. They do, if the cells in column B are imputed
manually, but in my case they are controlled by an 'IF' formula, where truth
is indicated by text and falseness is indicated by "", to create a blank
cell. Both these formulas interperate "" as text.

I may need to find a new way to do this altogether, unless someone knows of
a way to manipulate either of these formulas to exclude the blank cells, in
other words, list the only cell that is not "".

A formula that would work would also do the following:

case 1: cell should say "Text 3"
A
false
false
Text 3
false

case 2: cell should say "Text 2"
A
false
Text 2
false
false

Thanks for your help, though, in truth you did answer my question!

 

[RichardSchollar]

Adam

Try this variant:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Note that this formula doesn't use whole column refs (it is 1 row
less).

Best regards

Richard

 

[Guest]

works like a charm!

thanks, Richard

Adam

Try this variant:

=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)

Note that this formula doesn't use whole column refs (it is 1 row
less).

Best regards

Richard