Named Ranges - Using the same name on different sheets

[krame00]

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...

 

[Caliche]

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

 

[krame00]

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?

 

[Yogi Anand]

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!

 

[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!

No Luck... Do I need the elipses in the "refers to" box?

 

[Blue Hornet]

No, just:
=!$C$9:$E$11

(to use Yogi's example)

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

Chris

 

[krame00]

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

 

[Blue Hornet]

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

 

[FastExcel]

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.

 

[krame00]

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!