I have a number of Workbooks used by others in my organisation.
Often they will copy cells that have range names from one workbook to into the workbook I supplied. In the end a workbook may have several instances of the same range name but referencing different workbooks some of these on systems not available to me. When I use my Query it errors out because of such references.
Can I prevent the duplication of range names occurring. I want to stop new instances of the same range name from being added to the workbook?
And if cannot prevent this can I have a test when using Combine and Transform in my Power Query to test if there is more than one instance of a range name in each of the files in a folder?
Can you provide a mock-up XLSX file that illustrates this.
Q. Does the original name range have fixed coordinates or another attribute to distinguish it from the duplicates?
It is very difficult to send a working mock-up.
The scope of range name that I want to refer to is the workbook - the current workbook. In this way Excel should stop others from being created. However, if the user say copies an object or formula from another workbook they are asked if they want to bring the name into the curent workbook even o if there is one with the name already. The outcome is that the workbook has eth original and another with an external reference.
This forum is full of volunteers that give up their own time to help others, all we ask in return is that if you post a question you also provide as much detail and mock up of what you are trying to achieve.
I understand the issue you’ve described but without a sample file that allows me to explore avenues to help you deal with that - I’m unable to provide any assistance.
@Melissa
Thank you for your message.
I do value the input from the forum members. So in the next few days I will try an mockup something to put on my thread.
Kind Regards
Allister