newbie seeks excel help - please!!

  • Thread starter Thread starter earthgirluk
  • Start date Start date
E

earthgirluk

hi
hope someone can help, i have a spreadsheet that has daily sales, monthly
sales and yearly sales which (forgive my explanation i am a complete newbie
lol) when i enter a new value in the daily sales, i want the monthly sales
to increases but also add the previous value, and the same with the yearly
cell, if you know what i mean.

A1 A2 A3
daily sales monthlysales yearly sales
1 2 3

at the moment im trying =A1+A2 for cell A2, and =A2+A3 for cell A3, but i
keep getting a circular reference message,
ive tried going to tools>options>calculation and doing the iterations but to
be honest i havent got a clue, it seems such a simple formula, ive looked on
the net and macros have been mentioned.

any help would be really appreciated

thanks

tracie
 
Using V BA


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$A$1" Then
With Target
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
.Offset(0, 2).Value = .Offset(0, 2).Value + .Value
End With
End If

ws_exit:
Application.EnableEvents = True
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.
 
You need to do this with a macro.right click sheet tab>view code>copy/paste
this>modify to suit>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" And IsNumeric(Target) Then _
Range("a2").Value = Range("a2") + Target
Application.EnableEvents = True
End Sub
 
Back
Top