Balance Forward Method

[Anne Troy]

Suppose I have my running checkbook balance in column D.
I may continue to add additional rows...

Suppose over in H1, I want to always be able to see that running balance.
How can I always get H1 (for instance) to show me the last value in column D?

[Jacob Hilderbrand]

=INDEX(D:D,MATCH(9.99999999999999E+307,D:D,TRUE))

[Anne Troy]

Egads, that's ugly. Thanks!!

[Jacob Hilderbrand]

Perhaps you would prefer a purtier vba solution?



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Long
n = Range("D65536").End(xlUp).Row
Range("H1").Value = Range("D" & n).Value
End Sub

[Anne Troy]

Nope. Client says it works terrific.

[Bob Phillips]

If you don't want ugly here is an array formula


=INDEX(D1:1000,MAX(ROW(INDIRECT("D1:1000"))*(D1:AD1000<>"")))

Although maybe not applicable in this case, this formula, unlike Aladin's is not numeric constrained

[Jacob Hilderbrand]

Nope. Client says it works terrific.

Glad to help. :beerchug:

Take Care