Running Totals

[Richard White]

Is it possible to keep a running total in a summary worksheet, while deleting previously entered information from the data input worksheet? For example, worksheet 1 is the data entry worksheet. Worksheet 2 is the summary worksheet. Cells A1, B1, and C1, have been used on the data input worksheet 1, and populate cell D4 of the summary worksheet, worksheet 2, with a total of 4. Is it possible for me to re-use A1,B1, and C1, with a diffferent set of data, a total of 3, for example, and have the summary worksheet not lose the previous total of 4 and add 3 to have a new total of 7 in cell D4? Formula? VBA code? Function?

 

[James Ravenswood]

Try this small event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Summary As Worksheet
Dim MyCells As Range
Set Summary = Sheets("Sheet2")
Set MyCells = Range("A1:C1")
If Intersect(MyCells, Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Summary.Range("D4").Value = Summary.Range("D4").Value + [Target]
Application.EnableEvents = True
End Sub


any time cells A1 or B1 or C1 in Sheet1 are changed, that new value will be added into cell D4 on Sheet2.


Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Richard White]

Thanks for your help. What formula goes in D4 the summary cell, of worksheet 2? Using the formula =Sum(A1:C1) returns the same value as it would without the macro.