Names | Localize names in cells

Excel supports two kinds of names, ones that belong to the whole workbook (global names) and ones that live on individual worksheets (local names).

The OAK | Names | Localize command turns existing global names into local names.

Why would you want to use it?

The fastest way to add a quantity of names to a workbook is to use the Excel Formulas | Defined Names | Create from selection command.  However, this command creates global names; sometimes you want local names instead.

Using the OAK | Names | Localize command immediately after the Excel Formulas | Defined Names | Create from selection command (with the selection undisturbed) will cause the global names just created to be transformed into names that are local to the active worksheet.

How to use it

To use this command, follow these steps:

1.Select a range containing labels that match the names you wish to localize;

2.Select OAK | Names | Localize;

3.A warning dialog is displayed, where you must press ‘OK’ to continue; remember that this command has no Undo facility.

Any global name that refers to the active worksheet and that is identified by a label in one of the cells will be deleted from the workbook and a replacement for it will be created locally on the active worksheet.

How it works

OAK looks at all the cells in the current selection.  Where a cell contains text that coincides with a defined name, the defined name is deleted and a new one that is of narrower scope (a local, sheet level name) is created with the same definition.

Wrinkles

This command does not work when applied to protected worksheets. You must manually unprotect them first.

This command does not follow the One-Cell rule.  This is to prevent all the names that happen to be mentioned on a worksheet from being localized whenever the command is executed with only one cell selected.  If you want to localize all the names mentioned on a worksheet, select the whole sheet (Ctrl+A does this quickly) before using the command.

Once a name has been localized, any references to it on other worksheets need to be qualified with the sheet name.  That needs to be done manually; OAK doesn’t do it automatically.

What you could do if you didn't have OAK

It is possible to mimic OAK's action by deleting a global name and then defining a new local name to replace it.  Excel's standard Formulas | Defined names | Define name command is all that is necessary to do it.  The process is fine for a handful of names but becomes very slow if many names must be converted.