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?

Figured it out...thanks.

"Josh O." wrote:

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?

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." wrote:

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?

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.

"JonR" wrote:

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." wrote:

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?