Excel 2000 "Dead" Chart Links

I've created a workbook which contains data on one sheet, and multiple charts
on one chart sheet that should be dynamically linked to the data sheet...but
aren't.

I placed multiple charts on one sheet by creating one "dummy" chart on the
sheet, then created embedded charts on the data sheet and moved them to the
chart sheet. After I'd done that (placing three embedded charts from the
data sheet on the chart sheet), I deleted the dummy chart and arranged the
other charts the way that I wanted them to display and print. I then used
this chart sheet as a template for other data sheets that would graph similar
results. I set the series ranges for each graph, and everything displayed
properly. But, if I change the data on the data sheet, the charts don't
automatically reflect the changes. The only way I can get them to display
properly is to re-select the data range for each series, but the chart still
will not dynamically change the data after that...I have to keep reselecting
the data range.

Have I accidentally done something to unlink the data? Help!

Is the calculation setting turned off? You can force a recalc by
pressing the F9 key. You can turn calculation back on by selecting
Options from the Tools menu, and on the Calculation tab, select Automatic.

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

Phoenix wrote:

I've created a workbook which contains data on one sheet, and multiple charts
on one chart sheet that should be dynamically linked to the data sheet...but
aren't.

I placed multiple charts on one sheet by creating one "dummy" chart on the
sheet, then created embedded charts on the data sheet and moved them to the
chart sheet. After I'd done that (placing three embedded charts from the
data sheet on the chart sheet), I deleted the dummy chart and arranged the
other charts the way that I wanted them to display and print. I then used
this chart sheet as a template for other data sheets that would graph similar
results. I set the series ranges for each graph, and everything displayed
properly. But, if I change the data on the data sheet, the charts don't
automatically reflect the changes. The only way I can get them to display
properly is to re-select the data range for each series, but the chart still
will not dynamically change the data after that...I have to keep reselecting
the data range.

Have I accidentally done something to unlink the data? Help!

Jon,

Calc is not turned off. I've tried manual recalc, automatic recalc, prayer
recalc (that didn't work), threat recalc (didn't work either), etc.

"Jon Peltier" wrote:

Is the calculation setting turned off? You can force a recalc by
pressing the F9 key. You can turn calculation back on by selecting
Options from the Tools menu, and on the Calculation tab, select Automatic.

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

Phoenix wrote:

I've created a workbook which contains data on one sheet, and multiple charts
on one chart sheet that should be dynamically linked to the data sheet...but
aren't.

I placed multiple charts on one sheet by creating one "dummy" chart on the
sheet, then created embedded charts on the data sheet and moved them to the
chart sheet. After I'd done that (placing three embedded charts from the
data sheet on the chart sheet), I deleted the dummy chart and arranged the
other charts the way that I wanted them to display and print. I then used
this chart sheet as a template for other data sheets that would graph similar
results. I set the series ranges for each graph, and everything displayed
properly. But, if I change the data on the data sheet, the charts don't
automatically reflect the changes. The only way I can get them to display
properly is to re-select the data range for each series, but the chart still
will not dynamically change the data after that...I have to keep reselecting
the data range.

Have I accidentally done something to unlink the data? Help!

There are unexpected ways that charts link to their source data.

1. If you have charts and data on the same sheet, and you copy the sheet
(with the charts), the charts on the new sheet link to the data on the
new sheet.

2. If you have a sheet with data and an embedded chart on a different
sheet, and you copy both sheets, the embedded chart links to the
original data sheet, not the copied data sheet.

3. If you have a sheet with data and an embedded chart on a different
sheet, and you move both sheets, the embedded chart links to the moved
data sheet.

4. If you have a sheet with data and a chart on a chart sheet, and you
copy both sheets, the chart links to the copied data sheet, not the
original data sheet.

It sounds like you were following the procedure in #2, and that was
causing your problems.

I find that the most reliable way to make a copy of a few sheets with
charts and data is to close the file, copy the file in Windows Explorer,
and reopen the copied file. Now the charts are linked to their
accompanying worksheet.

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

Phoenix wrote:

Jon,

Calc is not turned off. I've tried manual recalc, automatic recalc, prayer
recalc (that didn't work), threat recalc (didn't work either), etc.

"Jon Peltier" wrote:


Is the calculation setting turned off? You can force a recalc by
pressing the F9 key. You can turn calculation back on by selecting
Options from the Tools menu, and on the Calculation tab, select Automatic.

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

Phoenix wrote:


I've created a workbook which contains data on one sheet, and multiple charts
on one chart sheet that should be dynamically linked to the data sheet...but
aren't.

I placed multiple charts on one sheet by creating one "dummy" chart on the
sheet, then created embedded charts on the data sheet and moved them to the
chart sheet. After I'd done that (placing three embedded charts from the
data sheet on the chart sheet), I deleted the dummy chart and arranged the
other charts the way that I wanted them to display and print. I then used
this chart sheet as a template for other data sheets that would graph similar
results. I set the series ranges for each graph, and everything displayed
properly. But, if I change the data on the data sheet, the charts don't
automatically reflect the changes. The only way I can get them to display
properly is to re-select the data range for each series, but the chart still
will not dynamically change the data after that...I have to keep reselecting
the data range.

Have I accidentally done something to unlink the data? Help!

Jon,

Looks like I might have made a pig's ear out of this. I'll experiment with
some of your suggestions and let you know how things turn out. Thank you
very much for your time and responses!

"Jon Peltier" wrote:

There are unexpected ways that charts link to their source data.

1. If you have charts and data on the same sheet, and you copy the sheet
(with the charts), the charts on the new sheet link to the data on the
new sheet.

2. If you have a sheet with data and an embedded chart on a different
sheet, and you copy both sheets, the embedded chart links to the
original data sheet, not the copied data sheet.

3. If you have a sheet with data and an embedded chart on a different
sheet, and you move both sheets, the embedded chart links to the moved
data sheet.

4. If you have a sheet with data and a chart on a chart sheet, and you
copy both sheets, the chart links to the copied data sheet, not the
original data sheet.

It sounds like you were following the procedure in #2, and that was
causing your problems.

I find that the most reliable way to make a copy of a few sheets with
charts and data is to close the file, copy the file in Windows Explorer,
and reopen the copied file. Now the charts are linked to their
accompanying worksheet.

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

Phoenix wrote:

Jon,

Calc is not turned off. I've tried manual recalc, automatic recalc, prayer
recalc (that didn't work), threat recalc (didn't work either), etc.

"Jon Peltier" wrote:


Is the calculation setting turned off? You can force a recalc by
pressing the F9 key. You can turn calculation back on by selecting
Options from the Tools menu, and on the Calculation tab, select Automatic.

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

Phoenix wrote:


I've created a workbook which contains data on one sheet, and multiple charts
on one chart sheet that should be dynamically linked to the data sheet...but
aren't.

I placed multiple charts on one sheet by creating one "dummy" chart on the
sheet, then created embedded charts on the data sheet and moved them to the
chart sheet. After I'd done that (placing three embedded charts from the
data sheet on the chart sheet), I deleted the dummy chart and arranged the
other charts the way that I wanted them to display and print. I then used
this chart sheet as a template for other data sheets that would graph similar
results. I set the series ranges for each graph, and everything displayed
properly. But, if I change the data on the data sheet, the charts don't
automatically reflect the changes. The only way I can get them to display
properly is to re-select the data range for each series, but the chart still
will not dynamically change the data after that...I have to keep reselecting
the data range.

Have I accidentally done something to unlink the data? Help!