Re: possible bug: excel "names" conflict

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


In article <e4obuthhIHA.1944@xxxxxxxxxxxxxxxxxxxx>,
"CyberTaz" <typegeneraltaz1ATcomcastdotnet> wrote:

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.

--
HTH |:>)
Bob Jones
[MVP] Office:Mac

"Carl Witthoft" <carl@xxxxxxxxxxxx> wrote in message
news:carl-16D0FA.17074114032008@xxxxxxxxxxxxxxxxxxxxxxxxxxx
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?

--
Team EM to the rescue! http://www.team-em.com
.

Relevant Pages

  • Re: Export a reprot to Excel
    ... 'you can choose which worksheet will receive the data ... Dim objXLApp As Object 'Excel.Application ... Dim rst As DAO.Recordset ... 'replace with names and cell references that suit your template ...
    (microsoft.public.access.reports)
  • Re: Run-Time error 1004????
    ... > references. ... > I would expect ReplaceArray to contain:> ... > How do I modify the code above so that ReplaceArray is populated with the> data in the cells starting at A5 and continuing thru column D and the last> row on the worksheet? ... An>> added worksheet becomes the active sheet and Columns may reference the>> wrong sheet. ...
    (microsoft.public.excel.programming)
  • Re: Taking in account additional rows
    ... Then in another worksheet they ... the bottom x# of cells. ... >> worksheet that references certain columns/rows in the first spreadsheet to ... >> to have my reference cells recognize there is an extra row and update all ...
    (microsoft.public.excel.misc)
  • Re: Using formulae to change the colour of cells in Excel
    ... You may not use references to other worksheets or Workbooks ... You did not indicate anything that was not on the worksheet in your ... The cells that are selected when you enter the Conditional ...
    (microsoft.public.excel.misc)
  • Re: Exc 97; named range recognized in one sheet but not in other
    ... > refers to ... >I may be mistaken but it is possible that you have defined FISC02 twice in>the workbook, once as a global name and once as a name local to FY03 sheet. ... You may want to delete the> FISC02 names and redefine it again. ... > When you create a name for a range, if you precede the name with the> worksheet name then it will define it locally. ...
    (microsoft.public.excel.misc)