How to Resolve a Name Conflict for a Named Range in Excel
This tutorial will demonstrate how to resolve a name conflict in Excel.
When you try to copy two or more sheets from one file to another, if the files both have the same range names in them, a name conflict error could occur. Excel will warn you that the range name already exists and ask if you wish to rename the range name or use the existing version of that name.
Resolve the Name Conflict
Using Existing Versions
1. Select two or more sheets in the workbook that contain range names that are identical in the destination workbook.
2. Right-click on the sheet tabs and select Move or Copy.
The Name conflict box will appear.
3. Click Yes to use the existing version of the name. This mean that the range name coming in from the copied sheet will be removed and the original name in the destination sheet will be used.
Click Yes to All to use all the existing range names.
Rename Ranges
If you select No to rename the version of the range name, you can give the existing range name from the copied sheet a new name in the current sheet. This is useful if the range name has been used in formulas.
1. Click No to rename the range name.
2. Type in the New name.
3. Click OK.
As shown in the picture above, the original named range of December from the source sheet is renamed to Dec.
Duplicate Range Names
If you select a single sheet to copy or move to a book where some destination names are identical, you will not receive this warning. Excel will copy the sheet with its range names but alter the scope of each name to the sheet instead of the workbook.
Right-click on one sheet to copy to another open workbook and select Move or Copy.
No warning will appear. However, if you look in the name manager of the destination book, you will see that there are two versions of some of the range names. See the picture below. The original names have Workbook as the scope, while the copied names have a scope of just their own sheet (e.g., 3rdQuarter).
