cumulative total in a cell
Posted by Tiffany on February 15, 2001 7:58 AM
I have a worksheet in which I need to put a "Total To Date" in particular cells,and then update every week by adding the current week's amounts to arrive at a cumulative total to date. I have searched and searched for answers. I found two instances which involved writing a Macro that puts the cell total into the cell note and then adds the newly entered amount to arrive at the new value to place in the cell. Neither worked, as the cell note would keep the original number and never add anything that was changed in the cell. Help. My DH has to run this report once a week and is more frustrated than I am!!!
Posted by Dave Hawley on February 15, 2001 9:54 AM
Hi Tiffany
You could do this by using a circular refrence, but personally I wouldn't, as they can cause other problems. Below is some code I have written that will do this for you.
To use it, Right click on the sheet name tab and select "View Code" then paste this code over what you see:
Dim Amount
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Reset
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$1" And IsNumeric(Target) Then
Application.EnableEvents = False
Target = Amount + Target
Amount = ""
Application.EnableEvents = True
End If
Exit Sub
Reset:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "$A$1" And IsNumeric(Target) Then
Amount = Target
End If
End Sub
Change $A$1 to the cell you want to accumalate and the Push Alt+Q and save.
Good luck!
OzGrid Business Applications
