This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Reproducing the problem
What's happening
Two ways to remove the dialog boxes

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.

Bingo!

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!

If you're using range names, you're probably already proficient at Excel. Did you know that we offer advanced Excel courses, as well as VBA courses? You can see a summary of all of our classroom and online Excel training courses here.

Some other pages relevant to the above blog include:

Classes in Excel

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

The Dreaded Excel Name Conflict Loop

Reproducing the problem

What's happening

Two ways to remove the dialog boxes

Head office

London

Manchester