| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Series Named Range Problem | Charts and Charting in Excel | |||
| series w/ named range not shown when reopen chart | Charts and Charting in Excel | |||
| mult. w/sheets need identical view when opening (range, mag, cell) | Setting up and Configuration of Excel | |||
| Sheets named from range, and copy data? | Excel Discussion (Misc queries) | |||
| Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
Linear Mode
