display a single cell text from a range

Hi, 1st post for me this one as I have always managed to fanny my way around a problem.

Bit stuck with this one though. May be just a bit tired and brain not switched on?

Anyway, here goes...

I have a column of 20 cells. Only 1 of them has text in it. I want to scan down this column of cells and display at the bottom (i.e.cell 21) the text which is in the 1 cell that has text.
Only 1 cell in the column will have text at a time. I dont want a macro so a formula in the 21st cell would be great.

Anyone help.

Thanks in advance.

Royal Blue.

There's definitely a more eloquent way to do this, but it's all I've got for now :P see attached worksheet.

Oh, if you wanted to display the text, replace row 21 with :

Please Login or Register  to view this content.

Royal Blue,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Please amend title as explained above if you wish to continue this thread

Many thanks
VBA Noob

Eh...
I tried that formula, gives an error for everything in the alphabet after the letter "i"??

I wouldn't mind knowing what actually causes that.

You need to update the first post which I will do for you now

VBA Noob

try
=INDEX(A2:A20,MATCH("*",A2:A20,-1))

If you use Francis' formula then the lookup value needs to be something "greater" than any text that might be in the range so, to cover all eventualities

=LOOKUP(REPT("z",255),A1:A20)

although this would probably be sufficient

=LOOKUP("zzz",A1:A20)

Edit: I didn't realise that this has effectively been answered here

whenever looking for last text value in range use REPT("Z",255) as criteria... when looking for last numeric value it is safest to use 9.99999999999999E+307 ...

if you want to return ROW of last value use MATCH if you want value use LOOKUP ..

in both cases the binary search algorithm is employed so the criteria should exceed all known values to ensure the last is returned (hence use of BigText and BigNum criteria)

EDIT: this is just echoing dll's post.

Um, Franciz...
Your example starts with an i. If you change that i to a j, the formula returns an error. I had no idea why that was happening, so I asked for some help here:
http://www.excelforum.com/excel-gene...cognition.html

I'm not sayin', I'm just sayin'.

For sure, that's why I brought the info back here.
I had no idea what your formula was doing... being able to ask them peoples on here that know Excel inside out is the only reason I know anything about it at all!

mew!