relative Named Ranges based on worksheet

[Fr. Robert]

I have a worksheet that uses named ranges in it's formulas.

Is there a way to set the named range to be realative to the curent sheet?

Example currently one named range is

B_Class_Desc=Sheet1!$AJ$3

what I would like to do is have something like this
B_Class_Desc=CurrentSheet!$AJ$3

But since CurrentSheet! is supported is there a way to simulate it?

Thanks

 

[William Horton]

Or just plain old =Indirect("A1")
Enter that using menu path Insert / Name / Define and it will refer to cell
A1 on whatever the current sheet is.

 

[William Horton]

Try putting in the below using the menu path Insert / Name / Define:
=INDIRECT(ADDRESS(1,1,1,1,))
The above name will refer to cell A1 on whatever sheet it is placed in.

Hope this helps.

Bill Horton

 

[RagDyeR]

Read to the end to assign to *all* sheets at the same time.

http://tinyurl.com/276jhl


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a worksheet that uses named ranges in it's formulas.

Is there a way to set the named range to be realative to the curent sheet?

Example currently one named range is

B_Class_Desc=Sheet1!$AJ$3

what I would like to do is have something like this
B_Class_Desc=CurrentSheet!$AJ$3

But since CurrentSheet! is supported is there a way to simulate it?

Thanks

 

[Fr. Robert]

Thanks,

worked great

Or just plain old =Indirect("A1")
Enter that using menu path Insert / Name / Define and it will refer to cell
A1 on whatever the current sheet is.

 

[David C]

I have a worksheet that uses named ranges in it's formulas.

Is there a way to set the named range to be realative to the curent sheet?

Example currently one named range is

B_Class_Desc=Sheet1!$AJ$3

what I would like to do is have something like this
B_Class_Desc=CurrentSheet!$AJ$3

But since CurrentSheet! is supported is there a way to simulate it?

Thanks

Possibly the simplest way to accomplish what you want is to define the named
range scoped to the current sheet e.g.

Sheet1!B_Class_Desc = Sheet1!$AJ$3

Note that you have to select the correct "Name Scope" in 2007, or preface
the range name with Sheet1! if you are creating it in the 2003 name define
dialog.
If you copy this sheet to the same or to another workbook, Any range names
created in this manner will be replicated into the new sheet and the
references changed to the new sheet name.

One issue with the use of Indirect($AJ$3) is that this creates a volatile
range name which will force recalculation of the range address and any cell
formula that uses it everytime the worksheet updates. Volatile range
addresses are just as bad as volatile functions in cell formulas.