Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name Conflict pain (excel 2000)

    When i move/copy a tab from one workbook to another, I get stuck in this endlesss loop of Name Conflict dialog boxes asking me about a range name on the new sheet vs the existing sheet. The problem is, I cannot just stop the whole process and back out of the deal. There are a slew of names in the situation I'm citing, and I wind up having to crash Excel in order to escape. Is there any way known to avoid this? Just to have it smack you with an inescapable situation is obviously annoying.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Name Conflict pain (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Torquemada

    You have touched on one of the most annoying area. Its very powerful, but its not really thought out properly IMHO.

    So here is the low down as I understand it.

    Excel allows you to name ranges. But each of these names must be unique per workbook/worksheet. So if you use something like SomeName for the first time, you end up with a Workbook level name, that is this name is unique per this workbook.

    Then you add another name, SomeName on a worksheet and then this becomes a worksheet level name, that is its unique per this one worksheet, and from this point on, you have to stop using SomeName.

    You now can only use other strings, maybe SomeName1, or Some_Name, or maybe MySomeName.

    When you move a worksheet into a workbook Excel has to resolve these names for the ranges on this new worksheet, against what it has already on the existing worksheets and workbook level names.

    So the best way to resolve this, is to make the names unique enough by maybe adding the name of the worksheet, or better yet, develop a VBA macro that will rename all the ranges for you once you have moved the worksheet and thus this way you don't have to worry about what name conflict with what.

    If you need VBA help, please reply to this message, and I'm sure you will get what you are looking for.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Conflict pain (excel 2000)

    Thanks for the info. I was hoping there could be some keystroke combo that would surpress this action. It is a problem to have to try and remember to pre-check each sheet for conflicting names before you go ahead and move/copy the sheet. You would think that they would give you an out on that so you could back out of that endless question loop. Some sophisticated sheets have tons of names and once you're started, you can't stop. That's what I was trying to discover: a way to stop that madness! Thanks.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Conflict pain (excel 2000)

    You're close Wassim.
    I'll try and expand on your explanation.

    - Excel knows three kinds of names.

    1. Global names

    These are the names everyone knows: you have a range selected, choose Insert, name, define and type in the name to name the range (or you type the name in the name box to the left of the formula bar). If you type a name that already exists, the previous version is overwritten and you end up with (still) one global name pointing to the new location.

    2. Local names

    These are names local to a worksheet. They can be created in two ways.
    The first : Precede the name with the name of the worksheet: "Sheet1!TheName" whilst defining it.
    The second: creating a copy of a worksheet that has a range name inside the same workbook will force excel to add a local name on the newly copied sheet.

    3. Application namespace

    read about that on this page.

    If you want to check on what is going on with your names, download my name manager from the Excel MVP page below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts

    Re: Name Conflict pain (excel 2000)

    Instead of moving the sheet directly to an existing workbook with similar names why don't you first move the worksheet to a NEW temporary workbook. You won't get any conflicting names there. Once there you can can either easily remove all names if appropriate, or say adjust all of the names with an extra prefix to make them unique befor moving the sheet again to your original desired workbook.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •