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