How to bring text to cell from column

I want to solve such problem, maybe somebody will help:
Column B consists of many rows. In one of these rows may apear a text (may be different: A; B; C...etc.), while the rest rows remain blank. In cell A1 I want to show (bring) that text, that will apear somewhere in B column. Ho to program this?

So only 1 row in column B will have text?

If so, then

=LOOKUP(REPT("z",255),B:B) if it is text you want to bring back

=LOOKUP(9.99999E+307,B:B) if it is number

If you get error message, try replacing the commas (,) in the formulas with semi-colons (

Originally Posted by NBVC

So only 1 row in column B will have text?

If so, then

=LOOKUP(REPT("z",255),B:B) if it is text you want to bring back

=LOOKUP(9.99999E+307,B:B) if it is number

If you get error message, try replacing the commas (,) in the formulas with semi-colons (

Yes, only 1 row in column B.
Hmm... it doesn't work in my case. I don't know why. I have atached a simple file with that problem. Maybe you can view at it.

Please attach your file directly... We don't want to find your workbooks in 3rd party locations...use the paperclip icon in the Reply box and browse for your file.

It is because you have formulas in other cells returning blanks...

Try:

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

or

=LOOKUP(2,1/(B1:B24<>""),B:B)

with range adjusted to suit.

Originally Posted by NBVC

It is because you have formulas in other cells returning blanks...

Try:

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

or

=LOOKUP(2,1/(B1:B24<>""),B:B)

with range adjusted to suit.

Yes, I didn't mention, that blank cells hold formulas.
Now it looks through blank cells with formulas, but another problem appeared. Sometimes all rows with formulas in column B will be blank, so I want, that A1 cell also be blank. Now it gives "#N/A" then. How to overcome that?

Originally Posted by windknife

Try to add error handle.

Please Login or Register  to view this content.

HTH,
windknife

Thank you both. Now it seems that everything goes well