• 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...

  • 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

NVN

New Member
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.
 

Smallman

Excel Ninja
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
 

NARAYANK991

Excel Ninja
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
 

NVN

New Member
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.
 

bobhc

Excel Ninja
Have you tried as suggested by Narayan and PREFIX the cell reference with an underscore and not between the cell alphanumeric
 

NVN

New Member
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.
 
Top