Totals in one cell

[tl]

What would be the formula if possible for this setup I'm trying to use?

Cell A Cell B
2 67

When I input 2 in cell A, I want cell B to change to 69. I know how to do
that but the problem I'm running into is when I come back the following
month, and put lets say 6 in cell A, I want add 6 to the already 69 to make
75. So, another words almost like a running balance but within cell b.

So, another words, I want to add cell a to cell b to create a new total,
then take that new total the next month and add cell a to create a new total
but within cell b. Is this possible? Thank you.

TL

 

[Mike H]

Hi,

This works for A1 & B1 but you can change these to what you want. Right
click your sheet tab, view code and paste this code in on the right

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + Target.Value
Application.EnableEvents = True
End If
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[T. Valko]

See this:

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

 

[Gord Dibben]

Are you sure you want to do this?

Think about it after reading the following.

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

Also see John McGimpsey's site for VBA method and the same caveats as above.

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


Gord Dibben Excel MVP

 

[tl]

Would I be able to use this for a series of cells? I have several different
rows that I'm needing to keep a total of. I have it working for one total in
one cell in one row using the accumulator but I have several rows of totals.

TL

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Const WS_RANGE As String = "A1:A10" '<<<<<<adjust to suit

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End With
End If
End Sub


Gord Dibben MS Excel MVP

 

[tl]

Thank you Gord Dibben and everybody else that provided me with help.
Everything worked great!