PDA

View Full Version : [SOLVED] Balance Forward Method



Anne Troy
05-07-2005, 11:47 AM
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
05-07-2005, 11:56 AM
=INDEX(D:D,MATCH(9.99999999999999E+307,D:D,TRUE))

Anne Troy
05-07-2005, 12:01 PM
Egads, that's ugly. Thanks!!

Jacob Hilderbrand
05-07-2005, 12:13 PM
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
05-07-2005, 12:21 PM
Nope. Client says it works terrific.

Bob Phillips
05-07-2005, 01:32 PM
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
05-07-2005, 01:57 PM
Nope. Client says it works terrific.

Glad to help. :beerchug:

Take Care