I have a worksheet (Sheet1) with a collection of names, many referring
to complex formulas and some to ranges. Once I have everything debugged
for Sheet1 I want to add Sheet2, Sheet3, etc. and have all the names
duplicated but referring to the respective ranges in the new sheets.

For example, if in Sheet1 I have a formula Funct.A that refers to:

Sheet1!Range.1 + Sheet1!Range.2

when I insert/create Sheet2 I want Funct.A to refer to:

Sheet2!Range.1 + Sheet2!Range.2

Is there a way to get this to happen automatically, when Sheet2 is
created? (As part of this process Sheet2!Range.1 and Sheet2!Range.2
would also have to be created.)

I'm not clear regarding whether I should make Funct.A global or local,
and how to make the above happen (without a lot of manual work.)

There's a commercial product that apparently does this and a lot more
for $30 (you can find it by Google'ing "Excel: Create Same Name Sheet
Level Names"), but I won't be doing this often enough to want to pay
that.

When I rename Sheet2 to something more meaningful, I'd want the
references to be changed correspondingly. I think Excel already does
this.

I'm already using a great free product Name Manger 3.2, recently
recommended here by Hank Scorpio, but it doesn't seem to help with the
above.

I'm using Excel 2000, WinXP Pro.

Thanks in advance for any help!


--
jmg092548


------------------------------------------------------------------------
jmg092548's Profile: http://www.excelforum.com/member.php...o&userid=26119
View this thread: http://www.excelforum.com/showthread...hreadid=395378