how to make range names universal in workbook

[april]

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.

 

[Jim Thomlinson]

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/

 

[april]

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

 

[Dave Peterson]

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.