Named Ranges - Multiple Worksheets

[Josh O.]

I have created some named ranges on a worksheet...copied the worksheet and
made 3 more.

Looking at the named ranges in the Define Name options, I see that these
named ranges appear with the Name to the left and the tab name to the right...
In Sheet1 it appears as:
NamedRange1 'Sheet1'

In Sheet2 it appears as:
NamedRange1 'Sheet2'


I am referencing these names on another worksheet as ='Sheet1'!NamedRange1,
or as ='Sheet2'!NamedRange1...and it returns the correct values fine.

Now I need to add several more named ranges to all the tabs. Is there a way
to create this type of Named Range directly, without deleting all the tabs,
adding the named ranges and then duplicating that sheet over and over?

 

[JonR]

In the 'Define Name' dialog box you can type in just about anything. For
instance type 'Range1' in the 'Names in workbook:' field and '=Sheet2!A1:A35'
in the 'Refers to:' field.

Click the "Add" button. Then you can add another name and modify the
reference in the 'Refers to:' field. The trick is to click the 'Add' button,
not the 'OK' button. the 'OK' button closes the dialog box.
--
HTH

JonR

Please rate your posts

 

[Josh O.]

Thanks. I actually figured it out.

I was trying to figure out how to name a Local range specific to the
worksheet. The method you describe gives you a Global Named Range.

I appreciate the help though.