Re : Excel Creation of a New Copy and the Duplication of Names thereof

[tkt_tang]

Re : Excel Creation of a New Copy and the Duplication of Names thereof

1. Enter an Excel workbook and construct a table on Sheet1.

2. Construct a similar table on another Sheet.

3. The natural tendency is to create a new copy of Sheet1 and
instantly, such Sheet1(2) is inserted thereof.

4. Now, look at Insert > Name > Define Name for Sheet1(2).

5. There are names (originally created for Sheet1) being duplicated on
Sheet1(2).

6. However, the duplicated names on Sheet1(2) are qualified by a
column of "Sheet1(2)" listed on the right hand side of the Define Name
dialog box.

7. Then, Sheet1(2) is modified to meet certain requirements.

8. And then, Sheet1 is revised too with new names added. Such revision
is to be reflected on Sheet1(2).

9. That means that, Sheet1(2) is going to duplicate the newly added
names of Sheet1.

10. But, how is it possible to add those new names (by duplication) on
to Sheet1(2) ?

11. Note that it's most tempting to suggest that Sheet1(3) be produced
(just like Sheet1(2) being treated before that) ; But, No, it's not an
option (because, the modifications of Sheet1(2) hitherto must be
retained).

12. The requirement is to duplicate Names in another Sheet (to be duly
qualified by the local Sheet name).

13. Please share your experience.

14. Regards.

 

[Dave Peterson]

Names can be local to a worksheet or they can be global. To make my life
easier, I try to use local names whereever I can.

Insert|Name|define
In the names in workbook box, you can include the name of the sheet to make it
local:

Sheet1!myNameHere
or
'Sheet 1'!myNameHere2

If you're working with names, get Jan Karel Pieterse's (with Charles Williams
and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make working with names much easier.

It has lots of options--including converting from local to global or global to
local.