Display Last Figure
 

Hello Programmers,

Example: I have a list of figures ranging from A1:A12. These cell
contain formulas.
If I have currency values in A1:A5, A:6 down to A:12 will have $0.00
because those cells haven't been updated yet.

How do I display the last figure in the list in cell B1 (which in thi
case would be in A5); Without the $0's showing up as a value? Then a
A6 updates, and it's the last value in the list, then that's what
shown in B1.

Thanks,
EMo

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=47458

Display Last Figure
 

Try this formula

=INDEX(A1:A12,MAX(IF(A1:A12<0,ROW(A1:A12))))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"EMoe" wrote in message
...

Hello Programmers,

Example: I have a list of figures ranging from A1:A12. These cells
contain formulas.
If I have currency values in A1:A5, A:6 down to A:12 will have $0.00,
because those cells haven't been updated yet.

How do I display the last figure in the list in cell B1 (which in this
case would be in A5); Without the $0's showing up as a value? Then as
A6 updates, and it's the last value in the list, then that's whats
shown in B1.

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=474585

Display Last Figure
 

On Sun, 9 Oct 2005 08:00:33 -0500, EMoe
wrote:


Hello Programmers,

Example: I have a list of figures ranging from A1:A12. These cells
contain formulas.
If I have currency values in A1:A5, A:6 down to A:12 will have $0.00,
because those cells haven't been updated yet.

How do I display the last figure in the list in cell B1 (which in this
case would be in A5); Without the $0's showing up as a value? Then as
A6 updates, and it's the last value in the list, then that's whats
shown in B1.

Thanks,
EMoe


Formula Solution:

You could use this **array** formula in B1:

=OFFSET(A1,-1+MAX((A1:A120)*ROW(A1:A12)),0)

To enter an array formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

----------------------
VBA Solution

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens.

========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Dim Res As Range

Application.EnableEvents = False

Set AOI = [A1:A12]
Set Res = [B1]

For Each c In AOI
If c.Value = 0 Then
Res.Value = c.Offset(-1, 0).Value
GoTo X
End If
Next c

X: Application.EnableEvents = True

End Sub
======================

Neither routine checks for invalid entries but rather assumes that the only
entries will be 0's (to be ignored) or other values to be considered.



--ron

Display Last Figure
 

Thanks Guys,

I'll try both solutions.

Regards,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=474585