Named Ranges - Using the same name on different sheets

krame00

New Member
Joined
Oct 6, 2002
Messages
39
I have one workbook in which I created a named range on a sheet, and later made multiple copies of that sheet. Each of the copied sheets has that same named range in it - but it refers to the range in that sheet, not the original one.

I have another workbook which has multiple sheets, and I would like to create a named range in each sheet and use the same name for the range in each sheet, but Excel won't let me do it.

Is there a way to use the same range name in multiple sheets?

I have (my company has) Excel 97.

Thanks for any input...
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Yes: Precede the name of the range with the sheet name.

Example: Insert
Name
Define
Names in the book: Sheet1!MyName
Refers to: (your range in Sheet1)

and you could define the same name in Sheet2 with

Names in the book: Sheet2!MyName

HTH
 
Upvote 0
Thank you! Thank you! Thank you! Thank you!

Do ya think they could have put that in the help index anywhere? I must have wasted a few hours trying to figure that one out!

Is that something I should have inherently known, or is that an obscure discovery someone made?
 
Upvote 0
Hi krame00:

If you want to create a named range that covers a specific range of cells in each sheet (not one specific sheet) of the workbook, then you do it by:

1. activate a worksheet
2. select the range of cells of interest, say $C$9:$E$11
3. then with the cells selected do INSERT|Name|Define
4. key in the range_name
5. in the refers_to box enter ... !$C$9:$E$11 (note without any SheetName preceding !)

Let me know how it works out for you!
 
Upvote 0
Yogi Anand said:
If you want to create a named range that covers a specific range of cells in each sheet (not one specific sheet) of the workbook, then you do it by:

1. activate a worksheet
2. select the range of cells of interest, say $C$9:$E$11
3. then with the cells selected do INSERT|Name|Define
4. key in the range_name
5. in the refers_to box enter ... !$C$9:$E$11 (note without any SheetName preceding !)

Let me know how it works out for you!

No Luck... Do I need the elipses in the "refers to" box?
 
Upvote 0
No, just:
=!$C$9:$E$11

(to use Yogi's example)

Thanks, Yogi! That's another new one for me, too.

Chris
 
Upvote 0
That's how I tried it - but no luck. Maybe it doesn't work because I am working with Excel 97.

At any rate, the first method explained above worked just fine for me...

Thanks everyone!

Khana
 
Upvote 0
Well, I won't beat a dead horse, but ... I'm using Excel 97, also, and Yogi's method works fine for me.

If you don't make it an absolute reference, then it will 'appear' not to work, because it will be a sheet-by-sheet relative reference, but it does work in '97.

Chris
 
Upvote 0
There is a nasty bug you can hit if you use range names with refersto like
=!$C$9:$E$11

(if calculation is started from VBA you get the wrong answer because it always refers to the active sheet, and in excel 97 it crashes Excel if you do trace precedents on a formula containing such a name.).

So I strongly recommend that you do not use this kind of name:
use =INDIRECT("$C$9:$E$11") instead.
 
Upvote 0
Ahhh! Yes! I see it did work. I was just stumped because it did not show up in the Name Box.

It appears that the horse wasn't dead after all.

;)

God, I love the internet!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top