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!

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:

Name conflict box 1

Initially the dialog box asks you whether you want to create a new range name.

Then this one:

Name conflict box 2

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:

Workbook containing ranges

The workbook contains two worksheets called Inputs and Calcs.

If you press Ctrl + F3, you'll see that the workbook contains three range names:

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:

Copying worksheets

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:

Copying options

Choose the options shown here to copy the two worksheets (and crucially, their range names) from one workbook to the other.

Bingo!

Name conflict dialog box

The loop begins ...

What's happening 

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:

Method Notes
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!

This blog has 0 threads Add post