Workbook reference is reset to [0] in chart data source spec

[Ola]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi
I try to create some dynamic charts to a worksheet using named variables and it works great as long as I keep the workbook open, but when I close it and re-open it the workbook reference has been reset to [0] and the chart is not dynamic anymore. What do I do wrong?

In detail, this is what I do:
I define the names:
* chtRange to =Variables!$B$4, where Variables!$B$4 contains =COUNTA(Pace!C3:CU3)

* chtLen to =Variables!$B$3, where Variables!$B$3 contains 15

* chtPaceTotOpen to =OFFSET(Pace!$C$3ace!$CU$3;0;chtRange-MIN(chtRange;chtLen);1;MIN(chtRange;chtLen))

* chtPaceWeeks to =OFFSET(chtPaceTotOpen;-1;0)

Then I create a chart with
Y-values =stats.xlsx!chtPaceTotOpen
Category (X values) =stats.xlsx!chtPaceWeeks

It works great until I save, close and reopen the workbook. When I right click the chart and select "Select data source" this is what I get:
Y-values =[0]!chtPaceTotOpen
Category (X values) =[0]!chtPaceWeeks

and the chart does not update dynamically anymore.

Anyone have ideas how to fix this?
Thanks,
Ola

 

[Bob Greenblatt]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi
I try to create some dynamic charts to a worksheet using named variables and
it works great as long as I keep the workbook open, but when I close it and
re-open it the workbook reference has been reset to [0] and the chart is not
dynamic anymore. What do I do wrong?

In detail, this is what I do:
I define the names:
* chtRange to =Variables!$B$4, where Variables!$B$4 contains
=COUNTA(Pace!C3:CU3)

* chtLen to =Variables!$B$3, where Variables!$B$3 contains 15

* chtPaceTotOpen to
=OFFSET(Pace!$C$3ace!$CU$3;0;chtRange-MIN(chtRange;chtLen);1;MIN(chtRange;ch
tLen))

* chtPaceWeeks to =OFFSET(chtPaceTotOpen;-1;0)

Then I create a chart with
Y-values =stats.xlsx!chtPaceTotOpen
Category (X values) =stats.xlsx!chtPaceWeeks

It works great until I save, close and reopen the workbook. When I right click
the chart and select "Select data source" this is what I get:
Y-values =[0]!chtPaceTotOpen
Category (X values) =[0]!chtPaceWeeks

and the chart does not update dynamically anymore.

Anyone have ideas how to fix this?
Thanks,
Ola

Does it work if stats.xlsx is open? When you create the reference to
Stats.xlsx, is it open also? Does it show in the Links reference in the edit
menu?

 

[Ola]

stats.xlsx is the workbook all this is in, so it is open.

What I started off doing was to write
=Pace!chtPaceTotOpen
and
=Pace!chtPaceWeeks
in the Y and X value box respectively. But then Excel changes this to stats.xlsx instead of Pace (which is a sheet name) until the next time I open the Select data dialog.

Ola

 

[Ola]

....and Links... in the Edit menu is disabled.

 

[Bob Greenblatt]

stats.xlsx is the workbook all this is in, so it is open.

What I started off doing was to write
=Pace!chtPaceTotOpen
and
=Pace!chtPaceWeeks
in the Y and X value box respectively. But then Excel changes this to
stats.xlsx instead of Pace (which is a sheet name) until the next time I open
the Select data dialog.

Ola

It looks like you are using global rather than sheet names, which is why
Excel is changing it. This may be a bug, I have not tested it, but does it
happen when you save the file as xls as well as when it is xlsx?

 

[Ola]

Yes it does happen in xls files too, I tried that earlier because I thought that might help it, but it didn't. Are you able to see the problem on your side?
Thanks,
Ola

 

[Ola]

How do you mean using global instead of sheet names? I enter the sheet name and it is immediately replaced by the workbook name. Can I enter the sheet name in any other way? I do it without any quotation marks...

Ola

 

[Bob Greenblatt]

How do you mean using global instead of sheet names? I enter the sheet name
and it is immediately replaced by the workbook name. Can I enter the sheet
name in any other way? I do it without any quotation marks...

Ola

Once you define the name, and excel inserts the sheet name, delete the sheet
name.

 

[Gopi]

Were you able to solve this? I am getting the same problem in Office 2007 (XP).