Names | Redefine name

Top  Previous  Next

Excel 2007 introduced a Names Manager which provides the functions performed by OAK | Names | Redefine and will perform them considerably faster.  The command is valuable with earlier versions of Excel and provided also for consistency with earlier versions of OAK.

The OAK | Names | Redefine command intentionally resembles the Formulas | Defined names | Define name 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;

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 | Names | Redefine command carries out these three steps in a single operation, updating cell references across worksheets and workbooks.

To define a local name

Excel makes it rather difficult to define local names; you have to use the Formulas | Defined names | Define name command and type the name that you want along with a sheet prefix using the correct syntax.  

The OAK | Names | Redefine dialog has a simple check box that specifies the nature (global or local) of the name that should be added.  You can also define a name as local by simply giving it a name beginning with an exclamation point (!).  This is true for both the ReDefine Name and ReCreate Names commands.  This has the added benefit that any name beginning with “!” is immediately recognizable as a local name.

Note:  Even though Excel does allow you to create both a local name and a global name on the same worksheet with the same name, we strongly recommend that you not do this.

OAK5RedefineNames

How to use it

Adding a name

1select the range of cells you wish to name
2enter the range you would like to name (or click the button on the right and select the range);3
3select OAK | Names | Redefine;
4type the name you want in the New Name box;
5if you would like the new name to be a local name, tick the Local Name checkbox;
6press the ‘Add’ button.

Renaming a name

1select OAK | Names | Redefine;
2select the name that you wish to rename;
3type the name you want in the New Name box;
4press the ‘Rename’ button.

Alternatively, you can:

1select the range of cells you wish to rename;
2enter the range you would like to name (or click the button on the right and select the range);
3select OAK | Names | Redefine;
4type the name you want in the New Name box;
5press the ‘Rename’ button.

Deleting a name

1select OAK | Names | Redefine;
2select the name that you wish to delete;
3press the ‘Delete’ button.

Wrinkles

This command has been designed to mimic the behaviour of the equivalent command built into Excel.  In particular, it can be used to define new names, as well as re-define 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.

Naming Convention

There are certain guidelines which you should adhere to when constructing your naming convention; the following rules define what constitutes a valid name in OAK:

names must contain at least 4 letters so that they can be distinguished from references to columns;

names must begin with either a letter or an exclamation point (!); remember that any name beginning with “!” will be defined or created as local;

names cannot contain punctuation characters or spaces apart from underscores (_) or full stops (.).

If you violate any of these rules, an error dialog will be displayed.

Limitations

You cannot use this command to rename a name to a name that already exists.  You will receive a warning dialog if you attempt to do so.

When renaming a local name, the sheet reference at the start of the name must be retained in the new name to ensure the name is still defined as a local name, otherwise it will be defined as a global name.

You cannot use this command to redefine a local name as a global name or a global name as a local name in a single step.  In other words, the local name checkbox only applies when defining a new name.  In order to redefine the scope of a name, you must first delete the name in question and then define the new name.  However, this still leaves you with the task of manually updating all references to that name by either adding or deleting the worksheet name reference which comes before the actual name.

What you could do if you didn't have OAK

As mentioned above, Excel 2007 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.  

The name can be deleted and re-created by using Formulas | Defined names | Define name selecting the misapplied name and clicking Delete, and then renaming the range by selecting it and using Formulas | Defined names | Define name, typing in the correct name and clicking Add.  The formulas which need to reference the changed name then need to be found and changed to match with the new name.  

Excel’s Find and Replace (Ctrl+H) command may be able to speed the process up slightly, where you could select the block of formula that needs to be changed, pressing Ctrl+H, typing the old name as the Find what criteria and the new name as the Replace with criteria.