Last non-blank row

[awreet]

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.

 

[Teethless mama]

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

 

[Shane Devenshire]

Hi

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

Would be the best idea.

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Other shorter formulas for the last non-blank text cell are:


=INDEX(Sheet1!e1:e100,MATCH(TRUE,Sheet1!e1:e100<>""))

=LOOKUP(REPT("z",255),Sheet1!e1:e100,Sheet1!e1:e100)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Gary''s Student]

On Sheet1, pull-down:

Insert > Names > Define

pick a Name like le
and in the Refers To box, enter:

=IF(ISBLANK(Sheet1!$E$65536),LOOKUP(2,1/(Sheet1!$E$1:$E$65535>0),Sheet1!$E$1:$E$65535),Sheet1!$E$65536)

Then in any worksheet, the formula:

=le

will return the value in the last filled cell in column E of Sheet1.

 

[muddan madhu]

last non blank for text ( use ctrl + shift + enter )

=INDEX(E1:E100,MAX(ISTEXT(E1:E100)*ROW(E1:E100)))

 

[T. Valko]

=INDEX(Sheet1!e1:e100,MATCH(TRUE,Sheet1!e1:e100<>""))

If there are empty cells within the range that will return an incorrect
result.

=LOOKUP(REPT("z",255),Sheet1!e1:e100,Sheet1!e1:e100)

You can shorten that to:

=LOOKUP(REPT("z",255),Sheet1!e1:e100)

 

[awreet]

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)

 

[T. Valko]

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.