560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|Why the Excel range name conflict dialog box appears, and how to avoid looping|
|When you're copying worksheets containing range names, Excel can detect conflicts and take you into a seemingly endless loop of Yes/No dialog boxes. This blog explains why this happens, and how to get out of the 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.
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:
What it means
All copied formulae will point to the version of the Revenue range name as defined on the target workbook.
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.
After a fair amount of searching on t'Internet, I believe there are only two ways to get rid of these dialog boxes:
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 ...).
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!
Some other pages relevant to the above blog include:
25 Aytoun Street