We help IT Professionals succeed at work.

return last value cell

Ian Bell
Ian Bell used Ask the Experts™
on
Hi,

I would like the following formula to be modified to return the last value.
The below formula returns the last cell whether value or formula cell containing no value

=ROW(OFFSET(A11,COUNTA(A:A)-2,0))

Many thanks

Ian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Do you know if the last cell contains text or numbers? If so, you can use LOOKUP.
=LOOKUP(1E+40,A:A)           returns last number
=LOOKUP("zzzzz",A:A)          returns last text
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
And if you want to return either a number or text while excluding empty strings, you might use:
=LOOKUP(2,1/LEN(A:A),A:A)

Open in new window

Ian Bellretired

Author

Commented:
OOPS sorry I made a blunder. I meant to have asked for last row number which the formula I supplied gives but includes a fomula cell with no value
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I don't know why anybody would want the row number, but you can get it with:
=LOOKUP(2,1/LEN(A:A),ROW(A:A))

Open in new window

Ian Bellretired

Author

Commented:
Yes that does the trick
There are other columns that depend on row numbers to sum sum ranges hence the reason for wanting row numbers.
Thanks
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
There are other columns that depend on row numbers
That's another thing I really don't understand. I can't recall ever needing a row number for other formulas in workbooks I built for my own use.

I can understand needing an index number so you can use INDEX formulas. But row numbers are useful only with the INDIRECT function--and I use that seldom, and never with a variable row number.
Ian Bellretired

Author

Commented:
All I can say is it has been designed by EE and works for me.