Names | Recreate names

This page in 2007
Top Previous Next

The OAK4 | Names | ReCreate Names command intentionally resembles the Insert | Name | Create 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.

Why would you want to use it?

To rename a named range

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:

▪	misspell the name;

▪	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.

To alter the name, you need to

•	delete the name you don’t like;

•	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 Insert \| Name \| Create 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 OAK4 | Names | Recreate Names command carries out these three steps in a single operation, updating cell references across worksheets and workbooks.

To define a local name

Excel's Insert | Name | Create 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.

How to use it

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.

Follow these steps to create a new name or to rename an existing name:

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 OAK4 \| Names \| Recreate Names;

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;

6	Press the ‘OK’ button.

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.

Wrinkles

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 Insert | Name | Create command to create a block of names and then immediately follow it with the Insert | Name | Apply 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 OAK4 | Names | Apply Names 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.

What you could do if you didn't have OAK

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 Insert | Name | Create 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.