How do I keep chart/data link when copying worksheet
 

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.

How do I keep chart/data link when copying worksheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy at Trinity" wrote in message
...
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.

How do I keep chart/data link when copying worksheet
 

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy at Trinity" wrote in message
...
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.

How do I keep chart/data link when copying worksheet
 

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
_______


"Andy at Trinity" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy at Trinity" wrote in
message
...
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.

How do I keep chart/data link when copying worksheet
 

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

"Jon Peltier" wrote:

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
_______


"Andy at Trinity" wrote in message
...
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.
....

How do I keep chart/data link when copying worksheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy at Trinity" wrote in message
...
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

"Jon Peltier" wrote:

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
_______


"Andy at Trinity" wrote in
message
...
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.
...

How do I keep chart/data link when copying worksheet
 

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

How do I keep chart/data link when copying worksheet
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy at Trinity" wrote in message
...
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