 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / January 2008Series 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'!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  Signaturecaroline 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 _______ >I have named ranges on sheet1 to create my chart. > ChartDates for the X axis [quoted text clipped - 9 lines] > > Can you help? Thanks 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 > I have named ranges on sheet1 to create my chart. > ChartDates for the X axis [quoted text clipped - 9 lines] > > Can you help? Thanks SignatureAndy Pope, Microsoft MVP - Excel http://www.andypope.info 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 Signaturecaroline > Hi, > [quoted text clipped - 24 lines] > > > > Can you help? Thanks 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 > 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 SignatureAndy Pope, Microsoft MVP - Excel http://www.andypope.info 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 _______ > Hi, > [quoted text clipped - 23 lines] >> >> Can you help? Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.