Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to make range names universal in workbook
i have defined a range of values using the name to the left. however, the
names are valid only on that worksheet. how do i make those names valid in the whole workbook? thanks for your help. -- aprilshowers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to make range names universal in workbook
I assume that you are talking about named ranges (Insert - Name - Define).
if so there are 2 types on named ranges; Global and Local. By default named ranges are global and can be referenced from any sheet. In order to make a locally defined named range you need to preceed the name with the worksheet. 'Sheet1'!MyName Locally declared MyName Globally declared It is important to note that where a named range is defined both locally and globally with the same name then on the sheet where it is defined locally, the local name takes precidence. To manage local and global I recommend the following NameManager addin... http://www.oaltd.co.uk/MVP/ -- HTH... Jim Thomlinson "april" wrote: i have defined a range of values using the name to the left. however, the names are valid only on that worksheet. how do i make those names valid in the whole workbook? thanks for your help. -- aprilshowers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to make range names universal in workbook
i didn't use the insert-name-define method. i highlighted the range incuding
the name to the left of the values, then did the (insert-name-create) and checked the option of "create names in left column". i have about 15 range names created in this manner. the help section of excel told me how to define ranges on multiple worksheets - basically holding down the shift key and highlight the tabs to be included. this would work if i had just a few names, but i hate to go back over the 15 and repeat that process each time - probably a macro would be in order. thanks again -- aprilshowers "Jim Thomlinson" wrote: I assume that you are talking about named ranges (Insert - Name - Define). if so there are 2 types on named ranges; Global and Local. By default named ranges are global and can be referenced from any sheet. In order to make a locally defined named range you need to preceed the name with the worksheet. 'Sheet1'!MyName Locally declared MyName Globally declared It is important to note that where a named range is defined both locally and globally with the same name then on the sheet where it is defined locally, the local name takes precidence. To manage local and global I recommend the following NameManager addin... http://www.oaltd.co.uk/MVP/ -- HTH... Jim Thomlinson "april" wrote: i have defined a range of values using the name to the left. however, the names are valid only on that worksheet. how do i make those names valid in the whole workbook? thanks for your help. -- aprilshowers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to make range names universal in workbook
I'd get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp It'll make working with names much easier. And one of its many features allows you to localize or globalize names. ========= If you used a sheet level (or local) name that is identical on two different sheets, then you won't be able to use the same name (as a global name) on different sheets. One other thing, you can refer to these sheet level names by including the sheet name in your formula: If you have a local name on Sheet1 that is Sheet1!Test, then you can use this formula anywhere on sheet1: =test And you'll get the value from the cell in Sheet1 named Test. But if you want to use that name on a different sheet (say sheet2), you have to change your formula: =sheet1!test And you'll get the value from the cell in sheet1 named test. april wrote: i didn't use the insert-name-define method. i highlighted the range incuding the name to the left of the values, then did the (insert-name-create) and checked the option of "create names in left column". i have about 15 range names created in this manner. the help section of excel told me how to define ranges on multiple worksheets - basically holding down the shift key and highlight the tabs to be included. this would work if i had just a few names, but i hate to go back over the 15 and repeat that process each time - probably a macro would be in order. thanks again -- aprilshowers "Jim Thomlinson" wrote: I assume that you are talking about named ranges (Insert - Name - Define). if so there are 2 types on named ranges; Global and Local. By default named ranges are global and can be referenced from any sheet. In order to make a locally defined named range you need to preceed the name with the worksheet. 'Sheet1'!MyName Locally declared MyName Globally declared It is important to note that where a named range is defined both locally and globally with the same name then on the sheet where it is defined locally, the local name takes precidence. To manage local and global I recommend the following NameManager addin... http://www.oaltd.co.uk/MVP/ -- HTH... Jim Thomlinson "april" wrote: i have defined a range of values using the name to the left. however, the names are valid only on that worksheet. how do i make those names valid in the whole workbook? thanks for your help. -- aprilshowers -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
Can I group multiple spreadsheets to make universal changes? | Excel Discussion (Misc queries) | |||
universal copying over worksheet range | Excel Discussion (Misc queries) | |||
how do you make a summary page showing the workbook name with the excel sheet names | New Users to Excel | |||
Make A Universal Time Converter | New Users to Excel |