Name Conflict Dialog Box
This is the dialog box that is displayed when you copy a worksheet into a workbook that already contains those named ranges.
Excel automatically converts workbook level named ranges to worksheet level named ranges without any prompts.
This automatic conversion obviously causes a lot of confusion.
Workbook Level Named Ranges
If your existing workbook and the worksheet you are importing both contain the same workbook level named range, the workbook level named range from the worksheet you are inserting is automatically converted to a worksheet level named range.
Lets imagine you have two identical workbooks both containing a single worksheet with the same workbook level named range.
Open both the workbooks and move one worksheet into the other workbook.
You will notice that the worksheet is inserted without any problems.
However on further investigation you will see that the named range on the worksheet you have inserted has become a worksheet level named range.
If you import a worksheet (with named ranges) into a workbook (with no named ranges) then the named ranges are copied across as you would expect.
Worksheet Level Named Ranges
If the worksheet you are inserting contains a worksheet level named range that already exists then Excel will display ???
Lets imagine you have two identical workbooks both containing a worksheet level named range and workbook level named range with the same name.
The worksheet "Sheet" contains two named ranges.
This is basically a choice between using the name already defined in the workbook or allowing you to type in a new named range.
Yes - Pressing Yes will use the named range that is defined in the destination workbook.
No - Pressing No will display the Name Conflict dialog box and allow you to rename the named range to something else.
If there is a workbook level named range already with the same name ?
The option exists to change the names in the source formula before pasting into the new workbook.
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext