Series with named range on several identical sheets

[caroline]

I have named ranges on sheet1 to create my chart.
ChartDates for the X axis
Data1 for the values
I then entered
SERIES('Sheet1'!$B$3,'Sheet1'!ChartDates,'Sheet1'!Data1,1)
But Excel rewrote it that way
SERIES('WorkbookName.xls'$B$3,'WorkbookName.xls'!ChartDates,'WorkbookName.xls'!Data1,1)

This is not what I am after, because I am copying the sheet several times
and I would like each graph to get the data from its own sheet (each sheet
has a range named ChartDates and Data1.

Can you help? Thanks

 

[Jon Peltier]

If each sheet has its own ranges defined only on that sheet, Excel would not
rewrite the series formula. Excel only rewrites it if there is no such name
defined for the worksheet in the series formula. Change WorkbookName.xls
back to SheetName in the formula and see what happens.

- Jon

 

[Andy Pope]

Hi,

Assuming your sheet level named range is
Sheet1!ChartDates
the sheet level syntax should remain.

Your problem will occur when copying the sheet as the chart will replace
the sheet level named range with an array of static values.

Best stick with a workbook level reference that you revised to sheet
level after copying the sheet.

Cheers
Andy

 

[caroline]

Thanks Andy. I reached that conclusion myself but thought that perhaps there
would be a way round it.
Buy the way, the sheet level syntax does not remain even with only one
sheet. I am using Excel 2003. Strange!
Thanks a lot

 

[Andy Pope]

Hi,

It does for me even after saving, closing and re-opening.

Chart series formula
=SERIES(,Sheet1!CLAB,Sheet1!CDATA,1)

named ranges
Sheet1!CLAB :=Sheet1!$A$2:$A$5
Sheet1!CDATA :=Sheet1!$B$2:$B$5

Sure your named ranges are sheet level names?

Cheers
Andy

 

[Jon Peltier]

If you first save the sheet as a template (with working sheet-level names in
the chart data references), you can insert new sheets based on this
template, and the names will work on the inserted sheet.

- Jon