Duplicating worksheet-level names

[jmg092548]

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!

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

--
Jim
"jmg092548" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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 I highlighted the entire sheet using Control-A, then used Control-C and 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 may 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 last 35 that didn't get duplicated.)

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.

--
Jim

[Jim Rech]

>>To duplicate Sheet1 I highlighted the entire sheet using Control-A, then
>>used Control-C and
Control-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.

--
Jim
"jmg092548" <[email protected]> wrote
in message news:[email protected]...
>
> 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 I
> highlighted the entire sheet using Control-A, then used Control-C and
> 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 may
> 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 last
> 35 that didn't get duplicated.)
>
>> 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.
>>
>> --
>> Jim
>
>
> --
> jmg092548
>
>
> ------------------------------------------------------------------------
> jmg092548's Profile:
> http://www.excelforum.com/member.php...o&userid=26119
> View this thread: http://www.excelforum.com/showthread...hreadid=395378
>

[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 about Excel?" and then I looked in Excel's help file and it said that's the way to copy a worksheet (via Ctrl-drag, or from the "Edit - Move or Copy Sheet" menu.)

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

So how does one learn enough to avoid making faulty assumptions like 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

>>To duplicate Sheet1 I highlighted the entire sheet using Control-A, then
>>used Control-C and
Control-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.

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

--
Jim
"jmg092548" <[email protected]> wrote
in message news:[email protected]...
>
> Jim,
>
> Thank you VERY much! That solves my problem.
>
> I was about to ask "How does one learn this kind of in-depth info about
> Excel?" and then I looked in Excel's help file and it said that's the
> way to copy a worksheet (via Ctrl-drag, or from the "Edit - Move or
> Copy Sheet" menu.)
>
> So my assumption was the root of the problem, that copying a range
> (consisting of the entire sheet) was the same as copying the sheet.
>
> So how does one learn enough to avoid making faulty assumptions like
> 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
>
>
>> >>To duplicate Sheet1 I highlighted the entire sheet using Control-A,
>> then
>> >>used Control-C and
>> Control-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.
>>
>> --
>> Jim
>
>
> --
> jmg092548
>
>
> ------------------------------------------------------------------------
> jmg092548's Profile:
> http://www.excelforum.com/member.php...o&userid=26119
> View this thread: http://www.excelforum.com/showthread...hreadid=395378
>