FIND LAST CELL WITH DATA
 

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.

FIND LAST CELL WITH DATA
 

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


"sharon" wrote in message
...
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.

FIND LAST CELL WITH DATA
 

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 wrote:
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.

FIND LAST CELL WITH DATA
 

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

"Paul B" wrote:

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


"sharon" wrote in message
...
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.

FIND LAST CELL WITH DATA
 

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.

" wrote:

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 wrote:
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.

FIND LAST CELL WITH DATA
 

It took a bit but here you go

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

Sandy

sharon wrote:
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.

" wrote:

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 wrote:
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.

FIND LAST CELL WITH DATA
 

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
sharon wrote:
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.

FIND LAST CELL WITH DATA
 

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

------------------
mama no teeth

"sharon" wrote:

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.

FIND LAST CELL WITH DATA
 

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

--
Regards

Roger Govier


"sharon" wrote in message
...
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.

" wrote:

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 wrote:
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.

FIND LAST CELL WITH DATA
 

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

" wrote:

It took a bit but here you go

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

Sandy

sharon wrote:
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.

" wrote:

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 wrote:
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.

FIND LAST CELL WITH DATA
 

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

"Nobody" wrote:

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

------------------
mama no teeth

"sharon" wrote:

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.

FIND LAST CELL WITH DATA
 

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?

sharon wrote:
Worked like a charm!!! Thanks!!!!!

"Nobody" wrote:

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

------------------
mama no teeth

"sharon" wrote:

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.

FIND LAST CELL WITH DATA
 

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


"sharon" wrote:

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.

FIND LAST CELL WITH DATA
 

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

--
Biff
Microsoft Excel MVP


"PHOnos" wrote in message
...
< 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.
--
-PHOnos


"sharon" wrote:

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.