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

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" wrote
in message ...

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

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

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" wrote
in message ...

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

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

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" wrote
in message ...

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