??Names ranges, charts, and SERIES function arguments
There are two levels of range names. One is workbook level; the name of
the range is only the name, and it is referenced from anywhere in the workbook by the name. The other is sheet level; the name is preceded by the sheet name (as in 'Sheet_Name'!Range2_Name). When you define a sheet level name in code, you have to prepend the name with the sheet name: ActiveWorkbook.Names.Add Name:="'Sheet_Name'!Range_Name", RefersTo:= _ "=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !$A:$A)-1,1)" In a series formula, if you precede a workbook level name with the sheet name, Excel knows what you meant to say, and changes the reference to the workbook name. If it's a worksheet level name, Excel leaves it as is. If you're not defining sheet level names, how did you get some references in the series formula that Excel doesn't change from sheet name to workbook name? If you have defined a workbook level name in a worksheet, then make a copy of that sheet in the same workbook, the new sheet has a sheet level name matching the workbook level name in the original sheet. A global name "MyRange" that refers to Sheet1!$A$1 leads to "'Sheet1 (2)'!MyRange" which refers to 'Sheet1 (2)'!$A$1. If you do a lot of work with defined names, do yourself a favor and download Jan Karel Pieterse's Name Manager, a free add-in at http://jkp-ads.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wdeleo wrote: Greetings, I am using the following to define some named ranges that are used for charting: ActiveWorkbook.Names.Add Name:="Range_Name", RefersTo:= _ "=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' !$A:$A)-1,1)" After I define the names, I select a series and change the reference to the named range. When I hit return, for some of the arguments for some of the series, the sheet reference is changed to the name of the workbook and for others it stays as the sheet ref (I don't see a pattern in how it behaves). a) =SERIES('Sheet_Name'!$E$1,'Sheet_Name'!Range1_Name ,'Workbook_Name.xls'!Range2_Name,4) vs b) =SERIES('Sheet_Name'!$E$1,'Sheet_Name'!Range1_Name ,'Sheet_Name'!Range2_Name,4) I'd rather have it just reference the sheet (as in "b") so I don't have to worry about renaming the file and so forth (it seems to update automatically but I don't want to worry that it may not, and the workbook names are rather lengthy and cumbersome). Can someone explain why it changes to the workbook from the sheet ref, and how to set it to the sheet instead? Perhaps with my "Names.Add" statement? RefersToLocal vs RefersTo??? Thanks so much |
??Names ranges, charts, and SERIES function arguments
Thanks so much for your explanation and your time!!!
-----Original Message----- There are two levels of range names. One is workbook level; the name of the range is only the name, and it is referenced from anywhere in the workbook by the name. The other is sheet level; the name is preceded by the sheet name (as in 'Sheet_Name'!Range2_Name). When you define a sheet level name in code, you have to prepend the name with the sheet name: ActiveWorkbook.Names.Add Name:="'Sheet_Name'!Range_Name", RefersTo:= _ "=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' ! $A:$A)-1,1)" In a series formula, if you precede a workbook level name with the sheet name, Excel knows what you meant to say, and changes the reference to the workbook name. If it's a worksheet level name, Excel leaves it as is. If you're not defining sheet level names, how did you get some references in the series formula that Excel doesn't change from sheet name to workbook name? If you have defined a workbook level name in a worksheet, then make a copy of that sheet in the same workbook, the new sheet has a sheet level name matching the workbook level name in the original sheet. A global name "MyRange" that refers to Sheet1!$A$1 leads to "'Sheet1 (2)'!MyRange" which refers to 'Sheet1 (2)'! $A$1. If you do a lot of work with defined names, do yourself a favor and download Jan Karel Pieterse's Name Manager, a free add-in at http://jkp-ads.com. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ wdeleo wrote: Greetings, I am using the following to define some named ranges that are used for charting: ActiveWorkbook.Names.Add Name:="Range_Name", RefersTo:= _ "=OFFSET('Sheet_Name'!$A$1,1,0,COUNTA('Sheet_Name' ! $A:$A)-1,1)" After I define the names, I select a series and change the reference to the named range. When I hit return, for some of the arguments for some of the series, the sheet reference is changed to the name of the workbook and for others it stays as the sheet ref (I don't see a pattern in how it behaves). a) =SERIES('Sheet_Name'!$E$1,'Sheet_Name'! Range1_Name,'Workbook_Name.xls'!Range2_Name,4) vs b) =SERIES('Sheet_Name'!$E$1,'Sheet_Name'! Range1_Name,'Sheet_Name'!Range2_Name,4) I'd rather have it just reference the sheet (as in "b") so I don't have to worry about renaming the file and so forth (it seems to update automatically but I don't want to worry that it may not, and the workbook names are rather lengthy and cumbersome). Can someone explain why it changes to the workbook from the sheet ref, and how to set it to the sheet instead? Perhaps with my "Names.Add" statement? RefersToLocal vs RefersTo??? Thanks so much . |
All times are GMT +1. The time now is 11:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com