Names | Recreate names

The OAK Development | Names | Recreate command intentionally resembles the Formulas | Defined Names | Create from selection command built into Excel, but it enhances it in two respects.

•	it allows you to change names that you have misspelled or otherwise wish to alter

•	it allows you to define local names more easily.

From time to time you will find that you have used a name in a workbook and wish you’d called it something else. You might:

▪	want to change it so that it is consistent with other names that are related to it;

▪	realize that it just doesn’t accurately describe the area it relates to.

•	define a new one that you prefer, which may mean adjusting a label adjacent to the previously defined range, selecting that range, and reusing the Formulas \| Defined Names \| Create from selection command

•	alter all the formulas that use the old name, so that they instead use the new one.

The last step is quite a chore. There may be hundreds of formulas in the workbook that use the name, and they have all got to be identified and altered; these formulas may be widely scattered over the worksheets in the workbook. Furthermore, these formulas may even be referencing from different workbooks.

The OAK Development | Names | Recreate command carries out these three steps in a single operation, updating cell references across worksheets and workbooks.

Excel's Formulas | Defined Names | Create from selection command can make many names at one go, but they are always global names, associated with a workbook. It can't be used to make local names which have the narrower scope of a worksheet.

The ReCreate Names command can be used to create many single-row or single-column names in one operation. However, it takes a moment to ensure you have the correct structure in place. Labels that match the names that you wish to create must be placed in a single column or row on the worksheet adjacent to the ranges you wish to define.

If the labels are in a row, the individual columns in the selection are named according to the label that corresponds to each column. If the labels are in a column, the individual rows in the selection are named according to the label that corresponds to each row.

If an individual row or column within the selection is already named, this command will rename that row or column to match the corresponding label.

1	If there isn't already an existing label in a cell next to the range you wish to name, type the name you wish to create in a cell adjacent to the range you wish to name;

2	Select the range you wish to name including the row or column labels;

3	Select OAK Development \| Names \| Recreate;

4	Designate the location that contains the labels in the ReCreate Names dialog;

5	Select to which extent you want OAK to update the name references;

A name created by this procedure does not include the cells that contain the labels.

Any name beginning with an exclamation point (!) will be created as local to that worksheet. The dialog box also allows for local names to be created by ticking the Force Local option.

This command has been designed to mimic the behaviour of the equivalent command built into Excel. In particular, it can be used to create new names, as well as re-create existing ones, so it can be used as a complete, more capable replacement for the built in command.

You cannot use this command to define a new name, or redefine an already existing name, on a protected worksheet. If you redefine an already existing name while there are formulas on protected worksheets which reference that name, the pre-existing name in the formula will not be replaced. Previous versions of OAK would not allow any change to existing names referenced by protected worksheets at all.

The OAK ReCreate Names dialog box differs from the one in Excel in one small detail. Each of the choices presented, identifying a locations in which to create the name or names (for example ‘Top Row’, ‘Left Column’, etc.) is exclusive of the others. In Excel, however, this is not the case, and you can check more than one of the choices at the same time.

In Excel, if you use the Formulas | Defined Names | Create from selection command to create a block of names and then immediately follow it with the Formulas | Defined names | Defined name | Apply Names command, those names which were just created will be highlighted in the list of names. This particular feature is not replicated in OAK. However, the OAK Development | Names | Apply command makes it easy to apply many names at a time.

To perform the renaming, OAK has to identify all the relevant formulas that make use of one or more of the chosen names, and rewrite them one by one. This can be time consuming.

Excel 2007 introduced the possibility of altering a name and have all mentions of that name update instantly. The facility is offered through the Excel user interface in the form of the Names Manager, which provides renaming capabilities similar to those long offered by OAK. OAK Development | Names | Recreate is implemented in a way that uses this new capability, so long as the option is selected to apply to the whole workspace. Narrower scopes (active workbook, active worksheet or current selection only) still have to be done by OAK by altering formulas one at a time, because there is no way of limiting the scope of the action built in to Excel.

Excel 2007 has, at last, introduced facilities for adjusting names and defining local names easily. In earlier versions of Excel, redefining a name can be done without OAK but is a tedious process for large worksheets.

If it was Excel's Formulas | Defined Names | Create from selection command that was used to create the original version of the name, remember to alter the adjacent label from which Excel took the name so that it is consistent with the changed name.