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

Name conflict - (unknown) named ranges have appeared in my spreadsheet!

M

MBegg

New Member
Hi all,


Have come into a slightly odd issue this morning. I've had an ongoing little project to run some transformations on companies' regular reported financials in order to present them in the way I want to.


Today I tried to copy one of my input sheets within a workbook. On doing so I was presented with the error message:

"A formula or sheet contains the name 'P', which already exists on the destination worksheet. Do you want to use this version... etc etc".

On clicking yes, the same error message popped up for a different name, and proceeded to do the same for several hundred named ranges. Given that I haven't been using named ranges at all in these files I was a little confused!


Checking under Name Manager, there are huge number of named items, for most of which both the "value" and "refers to" fields show #REF!. Based on the names they do seem to have come from something in my company, but I have no idea what, and certainly I've not been using them myself. I can delete them, but then when I try again to copy a sheet it still gives me about 5 name conflicts, which aren't based on things in the Name Manager.


Apologies if I haven't explained this very well, but frankly I'm very confused about the whole thing! If anyone's seen anything similar or has an idea what might be the underlying issue, do let me know. Note this is just affecting these spreadsheets I've been working on, if I open a random other file it doesn't have the same problem.
 
Hi, MBegg!

Would you please copy and paste the named ranges and their definitions in both source and target workbooks? Better indeed consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Hi SirJB7, thanks for getting back to me on this - unfortunately it's not one of those problems where it's easy to come straight back with an answer!


Unfortunately I can't share the workbook as it's sensitive information, and not sure I can mock up a sample as I've no idea how the issue arose in the first place...


I've actually deleted the hundreds of invalid name ranges so don't have them to share, but they mostly had a real name with just a #REF! for both value and "refers to". Now there is nothing shown under Name Manager apart from 2 tables which are on other sheets. The problem that does remain though is confusing me even more - when I try to copy the sheet (and I've realised it's just one particular sheet in the workbook, not even all of them) it gives me the following error:


"A formula or sheet you want to move or copy contains the name '_1___123Graph_ACHART_1', which already exists on the destination worksheet. Do you want to use this version of the name?

- To use the name as defined in the destination sheet, click Yes.

- To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box"


The same error also appears for '_Key1', '_Sort', and 'Transition2'.


Note it does complete the copy if I press yes for all of these so it's not stopping me from working, it's just very irritating / confusing!
 
You must log in or register to reply here.
Back
Top