View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Murph Murph is offline
external usenet poster
 
Posts: 37
Default How do I recalculate cell only if reference cell value changes

Thanks Per.
Worked very well until I saved and reopened the spreadsheet. I saved it as
a .xlsm spreadsheet so the vb macro would save.
When I reopened it, the code was still there, but not functional. If I
delete the macro, save it, add the macro back in it works again until
saved/closed/reopened.

Is there something I need to do when I save or open the spreadsheet to
enable the macro automatically?

-murph

"Per Jessen" wrote:

Hi

You could use an event macro to calculate the running total in A1 when a new
value is entered in A2.

Right click on the sheet tab, select "View Code". Paste the code below into
the code sheet and close the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If IsNumeric(Target.Value) Then
Range("A1") = Range("A1").Value + Range("A2").Value
End If
End If
End Sub

Hopes this helps.

---
Per

"murph" skrev i meddelelsen
...
I have turned on the iterations (set to 1) for circular values.

here is the scenario:
I want to keep a runnng total of $$ used
I have a cummulative total cell (A1), and a used today (A2)
the formula in A1 is =A1+A2

this works well in the fact that it keeps the running total in A1. it
updates the total correctly when I put in a new value in A2.

The problem is that when any other cell value in the spreadsheet changes,
it
recalucaltes A1 again and adds the value in A2 to the total again.
It also does this same recalculation on saving or opening the spreadsheet
which I also need to turn off somehow.

I would prefer not to have users to do a manual calculation if possible