Last non-blank row

  • Thread starter Thread starter awreet
  • Start date Start date
A

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.
 
Hi

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

Would be the best idea.

Cheers,
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
 
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.
 
last non blank for text ( use ctrl + shift + enter )

=INDEX(E1:E100,MAX(ISTEXT(E1:E100)*ROW(E1:E100)))
 
=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)
Click to expand...

You can shorten that to:

=LOOKUP(REPT("z",255),Sheet1!e1:e100)
 
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)
 
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.
 
Back
Top