FIND LAST CELL WITH DATA

[sharon]

I am trying to display the last entry in a column. I am using the following
formula:

=LOOKUP(9.99999999999999E+307,E6:E4000)

My result shows up as zero if cell E4000 has nothing in it.

I am looking for a formula the looks at the entire E column and displays the
value of the last cell with a number in it.

 

[Paul B]

sharon, try,

=LOOKUP(9.99999999999999E+307,E:E)
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[sandydavies]

Here's a macro that will do it and show the value in a message box, is
this what you needed?


Sub LastValue()
Dim Mycell As Object
For Each Mycell In Range("E:E")
If Mycell.Value = "" Then
MsgBox Mycell.Offset(-1, 0)
Exit Sub
End If
Next
End Sub

Sandy

 

[sharon]

No, that doesn't work either. It still shows a zero.

 

[sharon]

No. I have a spreadsheet that tracks a running balance in column E. At the
top of the spreadsheet I want it to display the most current balance by
picking up the last entry in the column.

 

[sandydavies]

It took a bit but here you go

Cell F1: =INDIRECT((ADDRESS(COUNT(E:E),5)))

Sandy

 

[Die_Another_Day]

Here's an array formula that should work:
=INDIRECT("E" & MAX(IF(ISBLANK(E1:E10000),"",ROW(E1:E10000))))
Don't forget to press Ctrl+Shift+Enter

Charles Chickering

 

[Nobody]

try this:
=LOOKUP(2,1/(E6:E4000<>""),E6:E4000)

 

[Roger Govier]

Hi Sharon

It depends upon the formula you are using for showing the balance in
column E.
If the result of the formula =0 because some other criterion has not
been met, then your formula may show 0 as being the last value.
If the cells below the what you regard as being the last value are
blank, then your Lookup formula will return the correct value.

For example
=IF(D7<>"",E6+D7,0)
as opposed to
=IF(D7<>"",E6+D7,"")

The first case will return 0, the second case will return the value of
the last cell in the range containing a value as opposed to being null

 

[sharon]

When I enter this formula it displays the value in cell E220. However, the
last cell with a balance is E225.

 

[sharon]

Worked like a charm!!! Thanks!!!!!

try this:
=LOOKUP(2,1/(E6:E4000<>""),E6:E4000)

 

[Aladin Akyurek]

What value does

=LOOKUP(2,1/(E6:E4000<>""),E6:E4000)

in contrast to

=LOOKUP(9.99999999999999E+307,E6:E4000)

which appears to return a real 0?

 

[PHOnos]

< Lookup(L:L,E:E) > should return the data in the last cell of column E.
That said, sometimes it works, sometimes it doesn't and I don't know why.

 

[T. Valko]

=LOOKUP(9.99999999999999E+307,E6:E4000)

My result shows up as zero if cell E4000 has nothing in it.

That formula ignores empty cells (unless *every* cell in the range is
empty).

If you're getting a result of 0 then 0 is the last number in the range but
you may have turned off 0 display or maybe there's a custom/conditional
format set to hide 0s.