Running totals

[Tim_M]

Hello, as you may expect by my posting here, I have an Excel question. This could be something very simple and covered in one of the many excel resource I have available in the office, but I don’t even know where to look. I am working on a construction estimation work sheet for a friend. To make this really simple for explanation purposes, my friend on needs 3 cells each formatted very specifically.
Cell A3 will consist of the last amount paid for a specific item.
Cell B3 will consist of a running total of the amounts entered into Cell A3.

But here is where it gets tricky

I would like Cell A1 to be a control cell with some sort of generic text (the text isn’t important). Every time the text in Cell A1 is changed, the values in Cell A3 are added to Cell B3, and then cell A3 is set to 0 and ready for a new value to be entered, and the next time the control cell is modified the whole process repeats it self.

Thanks,
Tim Mailloux

 

[kskinne]

ok, this code should do what you are asking:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address <> "$G$5" Then Exit Sub
Dim LastSheet As Integer
Dim LastRow As Long
Dim x As Integer
LastSheet = Sheets.Count
LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For x = 1 To LastSheet
    For i = 3 To LastRow
    Sheets(x).Cells(i, 2).Value = Sheets(x).Cells(i, 2).Value + Sheets(x).Cells(i, 1).Value
    Sheets(x).Cells(i, 1).Value = 0
    Next i
Next x
Application.ScreenUpdating = True
End Sub

you will need to copy this code into your 'Lot 19' sheet module. this code is triggered by a change to cell G5 in the Lot 19 sheet, and in each sheet, it adds the amounts in column A to column B, then zeros out the amounts in column A.

hth
kevin