Formula help!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the formula to continually add a number inside a single cell with
itself over and over and total shows up in another cell? Almost like a
running total from 2nd cell from input in 1st cell.

i.e. Cell B2 has an a user input of 1, cell D2 totals 1.
Cell B2 has a user input of 2, cell D2 totals 3
Cell B2 has a user input of 5, cell D2 totals 8, etc.
 
No formula, you need VBA

Private mPrev
Const WS_RANGE As String = "H1:H10" '<== change to suit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = .Value + mPrev
End If
mPrev = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
mPrev = Target.Value
End If
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Alex

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

Otherwise go with Bob's event code which again has no paper trail to follow.


Gord Dibben MS Excel MVP

On Wed, 23 May 2007 00:36:01 -0700, Alex Vega <Alex
 
Back
Top