A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do I recalculate cell only if reference cell value changes?



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2009, 04:24 PM posted to microsoft.public.excel.worksheet.functions
Murph
external usenet poster
 
Posts: 35
Default How do I recalculate cell only if reference cell value changes?

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
  #2  
Old April 16th, 2009, 06:02 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default How do I recalculate cell only if reference cell value changes?

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


  #3  
Old April 16th, 2009, 06:47 PM posted to microsoft.public.excel.worksheet.functions
Murph
external usenet poster
 
Posts: 35
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



  #4  
Old April 16th, 2009, 08:04 PM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default How do I recalculate cell only if reference cell value changes

Murph,

I think you have to look at the security settings. Click the Office button
Excel Options Trust Center Trust Center Settings Macro Settings
Enable all macros. OK Exit Excel and open your file.

Does it work as desired now ? I assume it does.

As this security setting maybe isn't desireable, you can create your own
digital signatur to sign you macro project. Do write again if you need help
whith this subject.

Regards,
Per



"murph" skrev i meddelelsen
...
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




  #5  
Old April 16th, 2009, 09:52 PM posted to microsoft.public.excel.worksheet.functions
Murph
external usenet poster
 
Posts: 35
Default How do I recalculate cell only if reference cell value changes

This was just what I needed, Thanks Per

-murph

"Per Jessen" wrote:

Murph,

I think you have to look at the security settings. Click the Office button
Excel Options Trust Center Trust Center Settings Macro Settings
Enable all macros. OK Exit Excel and open your file.

Does it work as desired now ? I assume it does.

As this security setting maybe isn't desireable, you can create your own
digital signatur to sign you macro project. Do write again if you need help
whith this subject.

Regards,
Per



"murph" skrev i meddelelsen
...
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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.