possible bug: excel "names" conflict

[Carl Witthoft]

Here's what happened, using Office2004.

I set up some data, formulas and names in one sheet. (By 'names' I mean
the Insert/Name/Define thing). Call this sheet "First" , so for example
the name Range might be defined as First!A1:A10 and so on.

Then I wanted to try some modifications to my setup, so I made a copy of
this worksheet and named the copy something like "Second." While
Second was the front page, I opend the Insert/Name/Define dialog box,
and was somewhat surprised to find my names referring to stuff like
Range --> Second!A1:A10 .

Then I clicked on Range and defined a new variable, Range2, that pointed
to Second!A1:A10. At that point I tried deleting Range but could not.
I then closed the dialog box, brought First sheet to the front, and
reopened the naming dialog box. Now, the same Range variable pointed
to First!A1:A10 (everything I describe happened to all my 'names').
I believe, but can't recall for sure, that I clicked on each name that
pointed to First and clicked "add," even though they were already there.

Then I closed the dialog, brought Second sheet to the front, and
reopened the naming dialog box. My memory goes hazy here, but I
believe I still saw things like Range being identified as belonging to
Second. But, now I could delete Range, at which point it was still in
the list but not marked as belonging to Second.

So: is this by design or by accident? In particular, I'd always
thought that Names were a workbook-wide 'global' kind of variable. So
how does one intentionally assign a name to a single worksheet, and what
exactly did I cause to happen when I created a copy of the First
worksheet?

thanks for any ideas or help.

Carl

 

[CyberTaz]

Hi Carl -

It sounds like you *typed* the references for your range and neglected to
make those references Absolute. Either select the range of cells before
going to the dialog or - if you insist on typing - enter the Refers To: as

First!$A$1:$A$10

Actually, the quickest & easiest way to name a range is: Select the range,
click the reference in the Name Box, type the range name, then press return.

 

[Carl Witthoft]

Nope, all references are absolute. The cell ref itself doesn't change,
it's the worksheet reference that gets "doubled" I'm pretty sure
that the dialog box is failing to show the duplicate instances of the
names in existence. What I mean is: if every assigned name was listed,
I'd see

Range
refers to First!$A$1:$A$10

Range
refers to Second!$A$1:$A$10

It's only after creating a new name "Range2" and deleting the only
visible Range variable (in the Define dialog box) that the other,
"hidden" Range variable shows up in the list.

The only other thing I can offer is that the table of names displayed
shows a column for the duplicated names:

Foo
Range 'Second'


Or something like that. It's some sort of undocumented (so far as I can
tell) notification that the name is local to a worksheet instead of
global -- or that it's a dupe name is global but is sourced from Second,
as opposed to the first instance of the name.


I just did the same thing with OfficevX. the name 'one' shows up,
after I duplicated the worksheet, as

one 'sheet1 (2)'
two 'sheet2 (2)'

where one 'refers to' ='Sheet1 (2)'!$A$1

as before, if I bring sheet1 to the foreground, I see

one
two

one refers to sheet1!$a$1

I bring sheet1(2) to the foreground, open the dialog, and select 'one'
I click delete, and 'one' is still in the list, but now it refers to
sheet1!$a$1

 

[Bob Greenblatt]

Nope, all references are absolute. The cell ref itself doesn't change,
it's the worksheet reference that gets "doubled" I'm pretty sure
that the dialog box is failing to show the duplicate instances of the
names in existence. What I mean is: if every assigned name was listed,
I'd see

Range
refers to First!$A$1:$A$10

Range
refers to Second!$A$1:$A$10

It's only after creating a new name "Range2" and deleting the only
visible Range variable (in the Define dialog box) that the other,
"hidden" Range variable shows up in the list.

The only other thing I can offer is that the table of names displayed
shows a column for the duplicated names:

Foo
Range 'Second'


Or something like that. It's some sort of undocumented (so far as I can
tell) notification that the name is local to a worksheet instead of
global -- or that it's a dupe name is global but is sourced from Second,
as opposed to the first instance of the name.


I just did the same thing with OfficevX. the name 'one' shows up,
after I duplicated the worksheet, as

one 'sheet1 (2)'
two 'sheet2 (2)'

where one 'refers to' ='Sheet1 (2)'!$A$1

as before, if I bring sheet1 to the foreground, I see

one
two

one refers to sheet1!$a$1

I bring sheet1(2) to the foreground, open the dialog, and select 'one'
I click delete, and 'one' is still in the list, but now it refers to
sheet1!$a$1

This sounds like you are using global names vs. sheet level names. Add a new
sheet to the workbook, and look at its defined names. Do you see the names?
If so then you defined global names, not sheet level names. If this is not
the case, let us know.