Excel Running Total

[dhart999]

Hello Everyone,

Does anyone know how to get excel to accumulate a running sum? We have users entering numbers repetitively in the same cell (A2), we need a cumulative total to appear in A1 that just keeps adding the number entered in A2 to the total in A1.
Ex.
A1 beginning balance shows 100
A2 user enters 25

result: A1 should show balance of 125

A2 user enters 50
A1 balance should now show 175 (but it shows 150)

Every time the user enters a new number in A2, it *throws* out the previous number and only adds the new number to the beginning balance.

Can someone please help??

Thanks,
Doreen

 

[Nikita6003]

Try playing around with the Worksheet_Change(Target As range) event
This is triggered every time something changes on a sheet - the target refers to the cell/range of cells that was//were changed.

So you could code something like:

 If Target.Address = "A2" Then
  Range("A1").Value = Range("A1").Value + target.Value
End If

HTH

Cheers
Nikki

 

[dhart999]

Nikki,
Thanks for your help. Please bear with me... I'm a novice at VBA. Where do I put this code? Do I create a macro with the code in it? And how do I execute it on the excel spreadsheet?
Thanks again for your help.
Doreen

 

[Nikita6003]

Doreen, it's easy when you know where to look ;-)

Now you need to be aware that most xl versions will create a workbook with 3 sheets in it: Sheet1, Sheet2, Sheet3
Each of these sheets react to "Events" - something happens on a sheet and the appropriate event is triggered. That's useful for us programmers because it means we can "trap" those events and force either the user or excel to do something (anything ;-)) when we want them to.

How can you program events?
Just follow these steps:
1. Go to the VBA editor - either by pressing [Alt-F11] or by choosing TOOLS > MACRO > Visual Basic EDITOR
2. result: an extra Window opens - the VBA Windows On the left hand side of the VBA Window you'll see a sort of Explorer - this shows you - in the case of a standard new workbook where no sheets have been added and no sheets have been renamed:
VBAProject (Book1) to indicate the workbook
Under this, you'll see a folder icon called Microsoft Excel Objects (if you can't see it click the plus sign in front of VBAProject (Book1))
The Microsoft Excel Objects folder has a number of items under it - in the case of a new, unchanged workbook there will be 4:
Sheet1 (Sheet1)
Sheet2 (Sheet2)
Sheet3 (Sheet3)
ThisWorkbook
Now the items you want are Sheet1, Sheet2 and Sheet3.

Find out which of these sheets is the one where you want to keep changing cell A1
Say for instance this is Sheet1.
Now double click on Sheet1 (Sheet1) in the VBA Explorer - if all goes well the VBA Window will open up a nice blank code window, which belongs to Sheet1
In this window you'll see:
2 dropdown lists: the left hand one says (General), the right hand one says (Declarations)

You MIGHT have a single line of code - if you do, it'll read

 Option Explicit

. Just ignore it - Excel has added this automatically (it's used to force shoddy developers to explicitly declare any variables they want to use - this in case they make typos. If they DO make typos, Excel will growl at them)

To program the OnChange event you now need to:
A. Select WorkSheet from the left-hand dropdown. Excel will react by sticking this bit of code in the code window:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

You can ignore this & continue

B. From the right-hand dropdown, select Change/b]
You should now have this bit of code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

THIS is where you put the code above ;-)

HTH - and that it's clear what to do.
If not, you can send me a copy of the spreadsheet on Nikita6003@Hotmail.com & I'll give it a go

Cheers!
Nikki

 

[dhart999]

Nikki,
Thanks so much! I'm going to give it a shot and I'll get back to you.
Doreen