Duplicated named ranges on sheet copy

ITTom365 

Solution:

• In the source workbook, identify the formulas and/or functions that refer to globally scoped named ranges.
• Within each worksheet, open the Name Manager and create a locally scoped named range that contains the globally scoped named range. You will reference the global name using the indirect function: For example, if the range “Colors” is a global range stored on Sheet 1, and “Colors” is used in a formula or function on Sheet 2, create a local name on Sheet 2 (let’s call it Colors2) defined as =indirect(“Colors”). Do this for all instances on all worksheets you desire to copy / move to other workbooks.
• Where global names are referenced, substitute the local name you defined. Confirm your formulas still function. Continuing the example, if on Sheet 2 there is a function or formula that refers to the range “Colors” directly, substitute “Colors2” to refer to that range indirectly.  Instead of =vlookup([value],Colors,1,false), use =vlookup([value],Colors2,1,false). If you receive a #REF effort, go back and check the Name Manager for misspellings.
• Before moving any worksheet from your source workbook to other destination workbooks, you must remove all global names. In the example, even if Sheet 2 were copied to another workbook after completing step 3, all global names from the source workbook will be transferred to the destination, which will duplicate the ranges that already exist at the destination. I suggest copying or moving each worksheet to their own separate workbooks. Continuing with the example, after you have changed the references on Sheet 2 to “Colors2”, copy or move the sheet into a separate new workbook (let’s call it Workbook_Sheet2). Save this new workbook, open the Name Manager, sort by scope, and remove all names that are scoped to the source workbook. Depending on whether you save > close > reopen, or just save the workbook, the global ranges will appear with the scope “workbook” or show the file name of the source workbook from where it was copied.
• From Workbook_Sheet2, you will now be able to move or copy the desired worksheet to other destination workbooks. The formulas and functions will update automatically, as long as the range(s) at destination is(are) called exactly the same as they are at the source (i.e. “Colors”). There will be no external links back to the source workbook. The only ranges that will be added to the Name Manager at the destination are those that were locally scoped to the sheet you copied, so you will not have duplicates in the Name Manager. You will also note that, because there are no external links, Excel will not substitute #REF into the local ranges that are copied with the sheet. You will however see #REF when you copy the sheet to a new workbook (to remove global names) before you move it to the destination. Lastly, and perhaps what’s the nicest feature of this process, is that the parameters of the named ranges in the destination workbook are preserved. Meaning, if the range Colors in the source workbook was stored on Sheet 1 in cells A1:A5, and the range Colors in the destination workbook is stored on Sheet 10 in cells A10:A20, the copied worksheet will preserve the named range of Colors at the destination (Sheet 10 cells A10:A20).

Explanation:

It may not be a literal definition, but I think of indirect as a function that converts text into a reference; you feed it a text string, and Excel looks for an object (whether it’s a named range, table, graph, etc.) that matches the text.

Using indirect to refer to global names means that when the formulas/functions are copied/moved from one workbook to another, you aren’t copying references - you’re copying text strings. When those text strings arrive at the destination, the indirect function will look for an object at the destination that matches the text. On either end of the process – meaning at both the source workbook and the destination workbook – the indirect function successfully finds a reference to an object. In between the source and destination, the reference is preserved as a text string.

Disclaimer:

Indirect is a volatile function, and by storing it in the name manager, it may result in slow workbook performance if that name is used repetitively. This may motivate you to reconsider your functions/formulas that are most efficient. There’s a volume of good practices for ways to write efficient formulas, as well as information on how, among similar formulas (i.e. lookup formulas), certain functions are more efficient than others (for example, xlookup tends to be more efficient than vlookup or xlookup). You may also wish to take it a step further, and remove the indirect functions once the sheet(s) are copied to the destination.

I haven’t found any other responses for this question online. What inspired my approach to this was a post that said “do not fight Excel, but instead exploit what it has to offer.” Please let me know if this solution works or if you have any feedback.