Multiple charts each relative to own worksheet

**kaallen** · 10-20-2009, 07:56 AM

Hi,

I am trying to make a template worksheet within which there are charts that automatically update when new data is pasted into their source range. However, when I copy and paste my template into a new sheet, the original cell references (to the original sheet) are maintained in the chart source data fields. Is there a way to make the charts refer to the sheet they are in rather than the sheet where they were created? I can do this manually, but it will take a long time for the 8 charts per sheet and 50ish sheets!

Many thanks

**Andy Pope** · 10-20-2009, 08:08 AM

For me if I create a chart object on a sheet linked to cells on that sheet and then copy the worksheet the links automatically update to the new sheet.

How have you got the chart data setup to a chart?
How are you copying the template?

Can you post example of it not working.

**kaallen** · 10-20-2009, 09:10 AM

Here is the file, the chart in sheet 1 refers to data in sheet 1, but so does the chart in sheet 2. I copied the whole sheet by clicking the top left corner of the sheet, "ctrl + c", then "ctrl + v" in second sheet.

Thanks

**Andy Pope** · 10-20-2009, 09:17 AM

That would be copying the contents of the sheet, in which case the links would remain with the original sheet.

Copy the actual worksheet.

Right click tab and pick Move or Copy...

Or hold the CTRL key whilst selecting the tab and dragging to another place within the tabs.

**kaallen** · 10-20-2009, 09:27 AM

Perfect, so simple when you know how! Thanks very much.

**konkordski** · 11-04-2009, 07:45 PM

Can anyone please address this question for Office 2008 (for the Mac, Excel 12.2.1)?

The programmers seem to have "fixed" the issue -- in other words, even if you copy the whole worksheet to a new one within the same workbook, the links to the original worksheet stay in the chart. (MS Office, too "smart" for its own good once again!)