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.

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


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

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.

Hi,

Other shorter formulas for the last non-blank text cell a


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

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

If this helps, please click the Yes button

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.

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$655350),Sheet1!$E$1:$E$65535),She et1!$E$65536)

Then in any worksheet, the formula:

=le

will return the value in the last filled cell in column E of Sheet1.
--
Gary''s Student - gsnu2007k


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

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

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


On Dec 1, 11:02*pm, 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.

=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:e1 00)

You can shorten that to:

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


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Other shorter formulas for the last non-blank text cell a


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

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

If this helps, please click the Yes button

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.

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.

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.