Auto update the chart range for new sheets

[Access Joe]

Hey everyone,

I've got a workbook with data for multiple locations. There are 2 main
worksheets for each location:
Sheet #1 is a data entry sheet
Sheet #2 displays several charts/graphs using the data from the first sheet

I have to recreate these 2 worksheets for multiple locations but keep them
in the same workbook. I've tried copying the 2 sheets together and renaming
them. But when I copy/rename the sheets, sheet #2 with the charts does not
update the links to pull the data from the new 1st sheet (it continues to try
to pull data from sheet 1 of the set it was copied from).

Do you know if there is a 'quick' way to change the data range that all of
the charts on a page are pointing to? Right now, I have to go in and
re-select all of the cells for each data series.

Thanks!

 

[broro183]

Hi Joe,

Check out this page of Jon's:
http://www.peltiertech.com/Excel/Charts/ChgSrsFmla.html

hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Jayne]

I have the same problem in Excel 2007. I tried the Change Series Formula
solution from peltiertech.com even thought Excel 2007 wasn't one of the
versions listed. Unfortunately, when I ran it, it resulted in an error
message. The error was "Unknown error. Application-defined or
object-defined error."

Is there any other advice anyone can give on how to solve this problem in
Excel 2007? I did notice that if I copy the worksheet to a new work book and
then copy it back into the original workbook, everything updates just fine.
It's only when I copy from one worksheet to another in the same book that the
charts don't update. So we have a workaround, but it's a pain in the neck.
We didn't have this problem in the XP version of Excel. Any help would be
appreciated.