Chart data reference

[jbrumels]

Here is my problem
I have a bar chart on sheet1 of a spreadsheet; the source data for the chart is also on sheet1.
When I create a copy of sheet1 (now called sheet2) the chart on sheet2 still refers to sheet1 instead of sheet2 for its source data.
What I want is the chart on a particular sheet to always refer to the data on its own sheet for chart data.
Thanks

[NBVC]

copy the sheet by right-clicking Sheet1 tab and selecting Move or Copy...

Make sure the To Book field has your current workbook showing

Select where you want the copied sheet to go in the Before Sheet field

Check the Create a Copy checkbox.

Click Ok.

You can rename the sheet that is created.

[jbrumels]

I understand how to create a copy...the problem is that the chart on the copied sheet is still refering to the original sheet for its data. I want the chart on the copied sheet to refer to the copied sheet for its data not the original.

[NBVC]

When I copied it the way I described, then the chart refered to the data on the same sheet...

If you copy the sheet and paste it into a new sheet via Copy, Paste.... then the chart will refer to the original sheet.

[jbrumels]

Very interesting...when i follow your instructions in excel 2003 it works, excel 2007 refers to the original sheet. Any thoughts?

[NBVC]

Unfortunately (or maybe fortunately), I don't have 2007 so can't tell you for sure.... perhaps it's a bug?

Maybe someone with 2007 can reply with their opinion(s)?

[darkyam]

You could right-click on the tab->Move or Copy->select a location and make sure the Create a copy box is checked. Then you can rename the tab as desired. That will update the references in the chart.
NBVC, It does appear to be a strange bug...I have 2007, tried your method, and got the same results as the OP.