How do I keep chart/data link when copying worksheet

[Andy at Trinity]

I have a worksheet for each month within a file - each worksheet has data
tables, and some charts generated from that data. With Excel 2003, when I
copied a worksheet within the same file (for example, to create the July
sheet, by copying the June sheet) the charts in the new (copied) July sheet
correctly linked to the July data. When I do the same in Excel 2007, the
charts in the July sheet link back to the June data instead. I have looked
through the options and cannot see how to do this.

 

[Jon Peltier]

Is the data on the same sheet as the chart? In 2003, if the chart was on a
different worksheet, it remained pointing to the original sheet, not the
copied sheet; this even happened if you cook care to copy all sheets
together. A standalone chart copied at the same time as the data sheet
stayed linked to the copied sheet, not the original sheet.

- Jon

 

[Andy at Trinity]

Hi Jon,

Yes, for each worksheet, the charts are on the same sheet as the data, so in
2003 when I copy (for example) the June sheet and then rename it July and
start typing in the July data, the charts on this new July sheet are updated
with the new data. In Excel 2007, the charts on the July sheet are still
referenced back to the data on the June sheet, which doesn't make any sense
to me.

I tried turning off the "Cut, copy and sort inserted objects with their
parent cells" option under Excel Options/Advanced, but when I copied the
worksheet this time only the data parts were copied (from June into July) and
the charts didn't even appear on the July worksheet!

 

[Jon Peltier]

Andy -

I vaguely recall going through this a while back, but I couldn't reproduce
the issue. Were you using regular cell references or table references for
the chart source data? (I didn't just try the table references, because I
recall a lot of angst over this previously.)

- Jon

 

[Andy at Trinity]

I'm just using regular cell references.

One can reproduce this by doing the following:
1. Create a new Excel spreadsheet.
2. Type in a simple grid of data into Sheet1, e.g.

Task AA BB CC
Wk1 1 2 3
Wk2 4 5 6
Wk3 7 8 9

3. Create a "stacked bar" chart from this (just select the 4x4 grid, select
stacked bar and hit OK).
4. Using CTRL+cursor drag, create a copy of that sheet, i.e. Sheet1 (2).
5. As-is, this works fine - if you change the data in the 2nd sheet, the
graph in the 2nd sheet updates okay.
6. To break this, go back to Sheet1 and edit the graph contents, e.g.
right-click on the graph, choose "Select Data", then Edit each of the Wk1,
Wk2, Wk3 Series entries to extend them to include one extra data field, e.g.
from $B$xx:$D$xx to $B$xx:$E$xx. Type in a few random numbers into the E
column to fill out the matrix (these will appear fine as extra bars in the
Sheet1 graph).
7. Again, copy Sheet1, to create a Sheet1 (3)... you can now type any
numbers you like into the table in this latest copy and the graph doesn't
change at all - it's linked back to the data in Sheet1.

The only conclusion I can draw is that if you've manually changed any aspect
of the data series in the table, then Excel decides it must keep it linked to
the original. This isn't really ideal, in my view...

Andy

Andy -

I vaguely recall going through this a while back, but I couldn't reproduce
the issue. Were you using regular cell references or table references for
the chart source data? (I didn't just try the table references, because I
recall a lot of angst over this previously.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

....

 

[Jon Peltier]

Andy -

I've followed your protocol, but even after manually changing the series
source data through the dialog, the chart remains linked to its parent
sheet, not the original sheet. What version of Excel (including service
pack) are you using?

- Jon

 

[Andy at Trinity]

Hi Jon,

The Resources tab under Excel Options says it is: Microsoft(r) Office
Excel(r) 2007 (12.0.4518.1014) MSO (12.0.4518.1014)

This is a brand new Dell laptop and I run automatic updates, so it should be
up to date?

If it would help you I can e-mail you the example Excel spreadsheet in which
I reproduced the issue?

Andy

 

[Jon Peltier]

Ah yes, Excel 2007. I've had some issues with names as chart data source in
this version. When I get onto the other computer, the one with 2007
installed, I'll give it another go.

- Jon