Series with named range on several identical sheets
 

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'!C hartDates,'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
--
caroline

Series with named range on several identical sheets
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"caroline" wrote in message
...
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'!C hartDates,'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
--
caroline

Series with named range on several identical sheets
 

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 wrote:
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'!C hartDates,'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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Series with named range on several identical sheets
 

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
--
caroline


"Andy Pope" wrote:

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 wrote:
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'!C hartDates,'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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Series with named range on several identical sheets
 

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

caroline wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Series with named range on several identical sheets
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" wrote in message
...
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 wrote:
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'!C hartDates,'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

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info