Duplicating worksheet-level names

[jmg092548]

I have a worksheet (Sheet1) with a collection of names, many referrin
to complex formulas and some to ranges. Once I have everything debugge
for Sheet1 I want to add Sheet2, Sheet3, etc. and have all the name
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 i
created? (As part of this process Sheet2!Range.1 and Sheet2!Range.
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 mor
for $30 (you can find it by Google'ing "Excel: Create Same Name Shee
Level Names"), but I won't be doing this often enough to want to pa
that.

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

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

I'm using Excel 2000, WinXP Pro.

Thanks in advance for any help

 

[Jim Rech]

Copying a sheet duplicates local names. I started with Sheet1 with these
local names defined on it:

Sheet1!Range.1 =Sheet1!$C$7
Sheet1!Func.a =Sheet1!Range.1

The second name refers to the first as you seem to be doing. then I copied
Sheet1 and renamed it to Sheet2 and on it were defined:

Sheet2!Range.1 =Sheet2!$C$7
Sheet2!Func.a =Sheet2!Range.1

So I had 4 names at this point.

 

[jmg092548]

Thanks, Jim. That's the behavior I'd expect also.

However - when I duplicated Sheet1 which had 64 names defined locally
Excel only duplicated 29 of them to Sheet2. To duplicate Sheet1
highlighted the entire sheet using Control-A, then used Control-C an
Control-V.

I can't see any pattern to the 35 that weren't duplicated except that
fortunately, they were all range names and not function names. It ma
have helped that the function names were at the beginning of the list
since they all started with an underscore. (However, it wasn't the las
35 that didn't get duplicated.)

 

[Jim Rech]

To duplicate Sheet1 I highlighted the entire sheet using Control-A, thenControl-V.

Ahh, well that's really not duplicating a worksheet. To do what I did you
simply hold Ctrl down and drag the sheet tab to the right or left (or
right-click the tab and select Move or Copy, which is slower). Make sure
you release the mouse button before you release Ctrl. Then you get all the
local names.

If you merely copy a range (even if it's the entire sheet's range) you just
get the names that are used in formulas. i.e., the names needed on the new
sheet for it to work.

 

[jmg092548]

Jim,

Thank you VERY much! That solves my problem.

I was about to ask "How does one learn this kind of in-depth info abou
Excel?" and then I looked in Excel's help file and it said that's th
way to copy a worksheet (via Ctrl-drag, or from the "Edit - Move o
Copy Sheet" menu.)

So my assumption was the root of the problem, that copying a rang
(consisting of the entire sheet) was the same as copying the sheet.

So how does one learn enough to avoid making faulty assumptions lik
that?? ... feel free to take that as a rhetorical question
but if you have suggestions I'd be quite open to hearing them!

Thanks again,

Jim

 

[Jim Rech]

feel free to take that as a rhetorical question

Great, I will! Since it's unanswerable<g>. Even the most experienced users
have faulty assumptions in their Excel mental toolset. I guess you have to
be alert to recognize when something that should happen if your assumptions
are true doesn't, to immediately challenge your assumptions. Easier said
than done.<g>