BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 14 June 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
The Dreaded Excel Name Conflict Loop
This blog is aimed at those who are familiar with this sequence of dialog boxes when you are copying worksheets. First this one:
Initially the dialog box asks you whether you want to create a new range name.
Then this one:
If you choose No, you get the chance to create a new range name.
Reproducing the problem
It's actually quite hard to reproduce this problem! One way is shown here. First download and unzip this workbook:
The workbook contains two worksheets called Inputs and Calcs.
If you press Ctrl + F3, you'll see that the workbook contains three range names:
Two range names point to the Inputs worksheet, and one points to the Calcs worksheet.
Save this workbook with a different name (say Revised.xlsx), and with both files open return to the Original.xlsx workbook and select both its workbooks to copy them:
Make sure you have both worksheets selected, then right-click to copy them.
Choose to copy both sheets to the Revised workbook, creating a copy:
Choose the options shown here to copy the two worksheets (and crucially, their range names) from one workbook to the other.
The loop begins ...
The reason you get the problem is that Excel is trying to resolve a conflict between two range names. For example between:
- the range name called Revenue in the Original workbook; and
- the range name called Revenue in the Revised workbook.
Here are the possible answers to the question posed:
|Answer||What it means|
|Yes||All copied formulae will point to the version of the Revenue range name as defined on the target workbook.|
|No||Excel will ask you to create a new range name, and will then create this new range name to replace the original Revenue one and redirect all copied formulae to point to it.|
It's worth noting that Excel is normally more intelligent than this. If you copy the worksheets individually rather than together, Excel will create local (worksheet-scoped) versions of the range name on the target workbook to avoid any conflicts, and you won't get this loop.
Two ways to remove the dialog boxes
After a fair amount of searching on t'Internet, I believe there are only two ways to get rid of these dialog boxes:
|Complete them||Answer the dialog box for each question which appears. The quickest way to do this is just to keep pressing Yes, and the quickest way to do this is to put a weight on your Enter key and go off to make a coffee (I'm indebted to Dave from my course yesterday for this tip ...).|
|Abandon Excel||Press Shift + Ctrl + Esc (quicker than Alt + Ctrl + Del) and choose to close Excel, losing any work that you haven't saved).|
I realise neither answer is particularly satisfactory, and would love to hear from anyone who has a better one!