Running Total without Updating too Many Cells

[kristee]

If i have 2 cells (A1 and A2), and A2 is the sum of all of the numbers
type into A1, what would the equation look like?
ie. A1 is being updated weekly, and A2 keeps a running total of th
numbers that have been typed into A1
Is this even possible to do? or would I have to just update A2 ever
time i update A1?
Thanks for your help

 

[Andy Brown]

A1 is being updated weekly, and A2 keeps a running total of the

numbers that have been typed into A1

Not 100% clear, maybe you could use event code.

Rightclick the sheet tab and "View Code".

Paste the following into the worksheet module -

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
Range("A2") = Range("A2") + Target
End Sub

HTH,
Andy

 

[Gord Dibben]

Kristee

Simple question with complex results.

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can
be done, not as a good solution. You would be much better off to
have another column so you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check
Iterations and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in
the Application D4 will update even if no new number is entered in
C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no
"paper trail" to follow. Any mistake in entering a new number in C4
cannot be corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4
and Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL
which does what you want without the re-calculation problem, but
again there is no "paper trail" for back-checking in case of errors
in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html


Gord Dibben Excel MVP