YTD calculation using same cell

[Guest]

I am creating a pipeline that has new data entered each week. I want the
weekly figure to automatically add on to the monthly figure.
Eg.
A B C
1 Weekly Monthly YTD
2 $100 $500 $600

The monthly will get cleared to $0 at the beginning of each month but I want
the YTD to continue increasing. So when I enter a figure into A2, I want it
to add on to C2 as well.
Help please - thanks

 

[Guest]

You will have to use VBA to do this but be warned: if you make an mistake on
the input to A2 this be reflected in the Monthly and Yearly totals which
would then have to be corrected manually. Also there is no audit trail of
your transactions.

So may want to review whether this is the best approach.

 

[Don Guillett]

right click sheet tab>view code>copy/paste this>
this will increase a2 by whatever you input into a2

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$A$2" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + oldvalue
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A2" Then
If IsNumeric(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
.Offset(0, 2).Value = .Offset(0, 2).Value + .Value
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End With
End Sub