FIND LAST CELL WITH DATA

[Guest]

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

 

[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

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[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

 

[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

 

[Guest]

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

 

[Guest]

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

 

[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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

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