Name Calculation in 2007

Don Wells · Post by **Don Wells** » 20 Nov 2010, 04:16

I do not have Office 2007 installed, but am trying to offer assistance to someone so configured. They have a chart that is not updating on some machines. I do not know whether they are using copies of the same file or are sharing a networked file. For the moment I am assuming that they are copies, or derivatives of copies, as the users are reportedly spread halfway around the world.
I read here in the MSDN Library, that "Names that are not referred to by any formula are not calculated even by a full calculation.".

The Source Data for the Value of the chart series is in the form "='Copy of Test Report Rev 004.xls'!Chart1C1"

The Named range Chart1C1 refers to = OFFSET(Chart1,Scroll,1,Zoom,1)

Chart1 refers to ='Chart Data'!$A$4

Scroll refers to='Chart Data'!$C$32

Zoom refers to ='Chart Data'!$C$33

Scroll and Zoom are both set scroll bars.

    On the problem machines the scroll bars do change the values of Scroll and Zoom but the chart does not change as a consequence; even when they press the recalc button on the Formulas ribbon.
    I have been unable to reproduce the problem on my 2003 installation.
    Is it possible that the problem would occur in a workbook saved as an xlsx 2007 file as a consequence of Chart1C1 changing but not being referred to by any formula?
    If this is the cause, could it be cured by referring to the named range in a hidden sheet (=Chart1C1), even though it would return an error?
    I have attached a copy of the workbook for your entertainment.

BigKev · Post by **BigKev** » 20 Nov 2010, 06:55

On my Office 2010 system pressing F9 chages the graphs according to the numbers for Scroll and Zoom. I have attached the workbook with a Module added and a Sub called Recalc that recalculates all the Worksheets. I then assigned this macro to the Scroll and Zoom slider controls. Changing the values of the controls now changes the Graphs.

This works for Excel 2010 but there is no reason it shouldn't work under 2007.

Cheers,
Kevin

Don Wells · Post by **Don Wells** » 20 Nov 2010, 11:56

Hi Kevin
As I read your post, you were unable to reproduce the problem. Am I correct?

BigKev · Post by **BigKev** » 20 Nov 2010, 13:42

Don Wells wrote:Hi Kevin
As I read your post, you were unable to reproduce the problem. Am I correct?

Sorry Don,

Yes, the problem existed before my fix. When I clicked the controls the graphs did not update until I pressed F9 to recalculate. As far as I can tell, this was caused by there being no Macro assigned to the controls.

Sorry again if my post was confusing.

Cheers,
Kevin

BigKev · Post by **BigKev** » 20 Nov 2010, 13:44

BigKev wrote:
Don Wells wrote:Hi Kevin
As I read your post, you were unable to reproduce the problem. Am I correct?
Sorry Don,

Yes, the problem existed before my fix. When I clicked the controls the graphs did not update until I pressed F9 to recalculate. As far as I can tell, this was caused by there being no Macro assigned to the controls.

Sorry again if my post was confusing.

Cheers,
Kevin

Sorry again, my mind has gone.

What I should have posted was, "No, I could reproduce the problem".

Don Wells · Post by **Don Wells** » 20 Nov 2010, 14:09

BigKev wrote:
BigKev wrote:
Yes, the problem existed before my fix. When I clicked the controls the graphs did not update until I pressed F9 to recalculate. As far as I can tell, this was caused by there being no Macro assigned to the controls.

Sorry again, my mind has gone. What I should have posted was, "No, I could reproduce the problem".

Sorry Kevin
I just realized that the file which I sent to you had the Recalculation switch set to 'Manual'. The users have encountered the problem with this switch set to 'Automatic'.
Please confirm whether or not you can reproduce the problem with the Recalculation set to 'Automatic'.

starcrwzr · Post by **starcrwzr** » 20 Nov 2010, 16:50

Hi Everyone,
I'm the user who submitted this question to the Lizard's Lounge and Don was kind enough to post it here. I wanted to add to the discussion, that the users that the charts quit working for, said they had worked initially, then they stopped working, with no known changes in their setups.

Scott B

BigKev · Post by **BigKev** » 21 Nov 2010, 07:17

Don Wells wrote:
BigKev wrote:
BigKev wrote:
Yes, the problem existed before my fix. When I clicked the controls the graphs did not update until I pressed F9 to recalculate. As far as I can tell, this was caused by there being no Macro assigned to the controls.

Sorry again, my mind has gone. What I should have posted was, "No, I could reproduce the problem".
Sorry Kevin
I just realized that the file which I sent to you had the Recalculation switch set to 'Manual'. The users have encountered the problem with this switch set to 'Automatic'.
Please confirm whether or not you can reproduce the problem with the Recalculation set to 'Automatic'.

I can confirm that with the Recalculation set to Automatic and without the Macro assigned the graphs DO change when the conrols are clicked.

My Calculations Options are shown in the attached picture.

Kevin

Calcluation Options in Excel.jpg

Don Wells · Post by **Don Wells** » 21 Nov 2010, 12:31

BigKev wrote:
I can confirm that with the Recalculation set to Automatic and without the Macro assigned the graphs DO change when the conrols are clicked.

My Calculations Options are shown in the attached picture.

Thanks Kevin

BigKev · Post by **BigKev** » 21 Nov 2010, 13:23

Any time Don. The fix I gave in the workbook will make the graphs updateno matter what the Recalculation settings of the user or the workbook however, the file must be saved as a 'Macro Enabled' workbook (.xlsm) in 2007 or 2010.

Don Wells · Post by **Don Wells** » 21 Nov 2010, 20:24

Hi Scott

I have attached a macro to the sliders which will update the charts --I believe independent of any settings other than macro security.

Please let me know how it works out.

File upgraded with this edit to protect against scrolling past the end of the data set.

rory · Post by **rory** » 22 Nov 2010, 12:45

Do the users have SP2 installed?

starcrwzr · Post by **starcrwzr** » 23 Nov 2010, 03:25

rory wrote:Do the users have SP2 installed?

Rory,
Don't know, will have to find out. Is this a potential problem?

Scott

rory · Post by **rory** » 23 Nov 2010, 08:22

Yes - there were many bugs in the initial release of Excel2007. particularly relating to charts. It's also generally regarded as better to use named ranges that don't begin with 'chart' when dealing with charts in 2007, as there have been some issues.

Post by **Jan Karel Pieterse** » 23 Nov 2010, 11:32

Didn't know that one Rory. Any references to documentation by any chance?

rory · Post by **rory** » 23 Nov 2010, 11:39

I've not seen any official documentation, but I know Jon P has mentioned it numerous times (for example here)

Post by **Jan Karel Pieterse** » 23 Nov 2010, 14:26

In my book, Jon mentioning a chart problem is as Official as I need

rory · Post by **rory** » 23 Nov 2010, 14:28

Snap!

starcrwzr · Post by **starcrwzr** » 24 Nov 2010, 13:41

This problem was corrected by the users installing the SP2 update to office. Thanks for all the input.

Scott

Eileen's Lounge

Name Calculation in 2007

Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007

Re: Name Calculation in 2007