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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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