Excel ask duplicate NAMES when duplicate a worksheets

[Kenneth Lam]

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)

 

[Dave Peterson]

There are workbook (global) level names and worksheet (local) level names.

Try this on sheet1 of a test workbook.

Select A3:B9 (any range will do)
type: Test1 (in the namebox to the left of the formula bar)

select C35
type: sheet1!test2

Now look at: Insert|Name|Define

You'll see the sheet name to the right of test2. This indicates that is local
to that worksheet (and that name (test2) can be used on any other worksheet as a
local name).

<In fact, you can use it on another worksheet as a global name, too.>

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

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

You get lots of options and can see differences very easily. It's well worth
the download.

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)

 

[Kenneth Lam]

Thanks. Maybe my English is not very good, you have mis-understood
what I mean.

Actually I know the different between a global and a local name. I am
asking why when I duplicate a worksheets (press CTRL and pull a
worksheet and create a duplicate copy of the worksheet), a local name
will be created from a global name in the new worksheet. (In my case,
the name ABOVE (global) is still there but a local name "INCOME
(2)!ABOVE" will be created in the new worksheet).

This happen to some of my workbook only. The global name are the same
in those workbook. But some of them will have this problem, and some
of them won't.

Anyway, thanks.

There are workbook (global) level names and worksheet (local) level names.

Try this on sheet1 of a test workbook.

Select A3:B9 (any range will do)
type: Test1 (in the namebox to the left of the formula bar)

select C35
type: sheet1!test2

Now look at: Insert|Name|Define

You'll see the sheet name to the right of test2. This indicates that is local
to that worksheet (and that name (test2) can be used on any other worksheet as a
local name).

<In fact, you can use it on another worksheet as a global name, too.>

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

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

You get lots of options and can see differences very easily. It's well worth
the download.

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)

 

[Dave Peterson]

You can only have one global version of the name in any workbook.

So if the name is going to exist on that new worksheet, it has to be local.

I don't think I've ever seen excel behave any other way.

Thanks. Maybe my English is not very good, you have mis-understood
what I mean.

Actually I know the different between a global and a local name. I am
asking why when I duplicate a worksheets (press CTRL and pull a
worksheet and create a duplicate copy of the worksheet), a local name
will be created from a global name in the new worksheet. (In my case,
the name ABOVE (global) is still there but a local name "INCOME
(2)!ABOVE" will be created in the new worksheet).

This happen to some of my workbook only. The global name are the same
in those workbook. But some of them will have this problem, and some
of them won't.

Anyway, thanks.

There are workbook (global) level names and worksheet (local) level names.

Try this on sheet1 of a test workbook.

Select A3:B9 (any range will do)
type: Test1 (in the namebox to the left of the formula bar)

select C35
type: sheet1!test2

Now look at: Insert|Name|Define

You'll see the sheet name to the right of test2. This indicates that is local
to that worksheet (and that name (test2) can be used on any other worksheet as a
local name).

<In fact, you can use it on another worksheet as a global name, too.>

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

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

You get lots of options and can see differences very easily. It's well worth
the download.

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)

 

[Kenneth Lam]

Thanks. But this problem only appear in 2 of my workbooks. All the
other have no problem.

Each workbooks have only 1 such global name called "ABOVE". No any
local name also called "ABOVE". (I have tried to list all the names
using FOR EACH .... ACTIVEWORKBOOK.NAMES ... and found only 1 such
name)

When I duplicate worksheet, only that 2 workbooks will warn me that
that name already exist and after I press "Y", it will create a local
name for me. I have to delete it everytime I duplicate a worksheets in
that 2 workbooks. All other workbooks don't have this problem.

Anyway. Thanks.

You can only have one global version of the name in any workbook.

So if the name is going to exist on that new worksheet, it has to be local.

I don't think I've ever seen excel behave any other way.

Thanks. Maybe my English is not very good, you have mis-understood
what I mean.

Actually I know the different between a global and a local name. I am
asking why when I duplicate a worksheets (press CTRL and pull a
worksheet and create a duplicate copy of the worksheet), a local name
will be created from a global name in the new worksheet. (In my case,
the name ABOVE (global) is still there but a local name "INCOME
(2)!ABOVE" will be created in the new worksheet).

This happen to some of my workbook only. The global name are the same
in those workbook. But some of them will have this problem, and some
of them won't.

Anyway, thanks.

There are workbook (global) level names and worksheet (local) level names.

Try this on sheet1 of a test workbook.

Select A3:B9 (any range will do)
type: Test1 (in the namebox to the left of the formula bar)

select C35
type: sheet1!test2

Now look at: Insert|Name|Define

You'll see the sheet name to the right of test2. This indicates that is local
to that worksheet (and that name (test2) can be used on any other worksheet as a
local name).

<In fact, you can use it on another worksheet as a global name, too.>

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

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

You get lots of options and can see differences very easily. It's well worth
the download.


Kenneth Lam wrote:

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)

 

[Dave Peterson]

I don't have any other suggestions.

Did you notice anything when you ran the Name Manager from Jan Karel Pieterse?

Thanks. But this problem only appear in 2 of my workbooks. All the
other have no problem.

Each workbooks have only 1 such global name called "ABOVE". No any
local name also called "ABOVE". (I have tried to list all the names
using FOR EACH .... ACTIVEWORKBOOK.NAMES ... and found only 1 such
name)

When I duplicate worksheet, only that 2 workbooks will warn me that
that name already exist and after I press "Y", it will create a local
name for me. I have to delete it everytime I duplicate a worksheets in
that 2 workbooks. All other workbooks don't have this problem.

Anyway. Thanks.

You can only have one global version of the name in any workbook.

So if the name is going to exist on that new worksheet, it has to be local.

I don't think I've ever seen excel behave any other way.

Thanks. Maybe my English is not very good, you have mis-understood
what I mean.

Actually I know the different between a global and a local name. I am
asking why when I duplicate a worksheets (press CTRL and pull a
worksheet and create a duplicate copy of the worksheet), a local name
will be created from a global name in the new worksheet. (In my case,
the name ABOVE (global) is still there but a local name "INCOME
(2)!ABOVE" will be created in the new worksheet).

This happen to some of my workbook only. The global name are the same
in those workbook. But some of them will have this problem, and some
of them won't.

Anyway, thanks.

There are workbook (global) level names and worksheet (local) level names.

Try this on sheet1 of a test workbook.

Select A3:B9 (any range will do)
type: Test1 (in the namebox to the left of the formula bar)

select C35
type: sheet1!test2

Now look at: Insert|Name|Define

You'll see the sheet name to the right of test2. This indicates that is local
to that worksheet (and that name (test2) can be used on any other worksheet as a
local name).

<In fact, you can use it on another worksheet as a global name, too.>

The best utility that I've ever seen for working with names is Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager.

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

You get lots of options and can see differences very easily. It's well worth
the download.


Kenneth Lam wrote:

I have added a NAME called "Above" where point to the cell just above
the current cell. The formula is "=INDIRECT("R[-1]C",)"

In some workbook, when I duplicate a worksheets, this name will remain
silent and work ok. But in some workbooks, when I first duplicate a
worksheets, the same name ABOVE will be duplicate and a new local name
(belongs to that new worksheet) will be created. If I further
duplicate that new worksheets in to a new worksheets, the third
worksheets will be warned that a dupicate NAME is existed and ask
whether refer to another name or use a new NAME.

Anything I have done wrong? Why the same NAME with the same formula
work in different way in different workbook.

Thanks.

(p.s. As I am using a Chinese version of Win and office, the wordings
I have used in the message may not match the wording in English
version. Hope that you understand what I mean.)