1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Range Names Conflict

Discussion in 'Ask an Excel Question' started by NVN, Nov 11, 2013.

  1. NVN

    NVN New Member

    Messages:
    3
    There is an excel file created in a compatible mode created in excel 2007 which has lots of range names created . some of the names are DEL001 , MAH001 , UP001 etc.

    Now the file has run out of rows and columns hence the need to transfer it to a newer version say 2010 . When a save as to the new version workbook is done an error like 'The name you entered is not Valid' the reasons The name conflicts with an excel built-in-name or tha name of another object in the workflow is thrown up.

    It asks whether you want to rename the range to a new name (which when done works fine) . However the name say 'UP001' etc is used a lot in the formulas and it would mean a lot of effort to find where all it is used and change the same to a new range name etc.

    A thought why it throws this is , a name conflict 'UP001' gets converted into UP1 for reasons unknown. I am using the Formula/Define names/ Name manager box for defining names and when I type 'UP001' and point it to a range and try to save it throughs a name conflict error. Somehow I feel that UP001 is looked upon as UP1 which is a cell address and hence the conflict.

    The old version did not have a column named 'UP' hence it worked very well.

    Is there any way I could handle this without having to change the Range Name to something else other than UP001?

    Please help.
  2. Smallman

    Smallman Excel Ninja

    Messages:
    1,217
    Hi NVN

    Have you thought about using Find and Replace ALL on the named ranges where there is a conflict. This is a simple workaround and should solve your problem. Find UP001 Replace with 'NewRngName' Replace ALL.

    Take care

    Smallman
    ThrottleWorks likes this.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    When ever Excel does not accept a name as a valid range name , the simplest thing to do is to prefix it with an underscore ; e.g. the single letter r or c is not acceptable as a range name ; prefixing it with an underscore , makes Excel accept it.

    Narayan
    ThrottleWorks likes this.
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,727
  5. NVN

    NVN New Member

    Messages:
    3
    Thanks Smallman , ultimately think will have to do that.
    Yes Narayan , plan to put an _ in between UP and 001. Excel does except that. Funny that UP001 matches excel range naming rules i.e. start with a alphabet , have some number , no spaces , no decimals. Do not understand ow DEL001 or UP001 match a cell name either the normal or R1C1 style.
    Thanks Hui read the link and the links in the links and learned sone about range naming coming to the conclusion that I have to provide new names , my existing range names in excel 2007 will not work.

    Thanks all.
  6. bobhc

    bobhc Excel Ninja

    Messages:
    3,404
    Have you tried as suggested by Narayan and PREFIX the cell reference with an underscore and not between the cell alphanumeric
  7. NVN

    NVN New Member

    Messages:
    3
    Do not want to do that as UP001 is just one of the 400 cell values which are also Range names. I have about 400 columns where a match is done on the range name formed from a formula . UP001 is the result of a formula and the value of a cell which I do not want to begin with an underscore.

Share This Page