I need to return the last non-blank row of text in a column

If you want to return the last TEXT entry in the range Sheet1!E1:E100:

=LOOKUP(REPT("z",255),Sheet1!E1:E100)

If the last *text* entry happens to be a formula blank ("") then that's what
will be returned. If you have formulas in the range that return formula
blanks and want to exclude those then let us know.


--
Biff
Microsoft Excel MVP


"awreet" wrote in message
...
Shane:

Thanks very much for the response. It still doesn't read through the array
to the last non-blank row, however. It returns the value in 'e1'. If I
replace that with 'e2', I get the value in e2 on the correct sheet, etc.
It's like Excel is ignoring the Max or array directions, or both.

Thanks,
Wayne (I'll use my name, I'm new to this group thing)

"Shane Devenshire" wrote:

Hi

index('Sheet1'!e1:e100,max(row('Sheet1'!e1:e100)*( 'Sheet1'!e1:e100<"")))

Would be the best idea.

Cheers,
Shane Devenshire

"awreet" wrote:

Hello:

I need to return the last non-blank row of text in a column from one
worksheet to a summary worksheet at the front of my workbook.

='Sheet1'!E4 works to return text from cell E4 of Sheet 1.

=index(e1:e100,max(row(e1:e100)*(e1:e100<""))) supposedly returns the
last non-blank text from a row in a column.

However, when I combine the two I get an error:

='Sheet1'!index(e1:e100,max(row(e1:e100)*(e1:e100< "")))

Can anyone educate me? Any feedback is appreciated...Thanks.