The OAK4 | Names | ReDefine Name command intentionally resembles the Insert | Name | Define 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 OAK4 | Names | ReDefine Name 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 Insert | Name | Define command and type the name that you want along with a sheet prefix using the correct syntax.
The OAK4 | Names | ReDefine Name 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.
How to use it
Adding a name
| 1 | select the range of cells you wish to name |
| 2 | enter the range you would like to name (or click the button on the right and select the range);3 |
| 3 | select OAK4 | Names | ReDefine Name; |
| 4 | type the name you want in the New Name box; |
| 5 | if you would like the new name to be a local name, tick the Local Name checkbox; |
| 6 | press the ‘Add’ button. |
Renaming a name
| 1 | select OAK4 | Names | ReDefine Name; |
| 2 | select the name that you wish to rename; |
| 3 | type the name you want in the New Name box; |
| 4 | press the ‘Rename’ button. |
Alternatively, you can:
| 1 | select the range of cells you wish to rename; |
| 2 | enter the range you would like to name (or click the button on the right and select the range); |
| 3 | select OAK4 | Names | ReDefine Name; |
| 4 | type the name you want in the New Name box; |
| 5 | press the ‘Rename’ button. |
Deleting a name
| 1 | select OAK4 | Names | ReDefine Name; |
| 2 | select the name that you wish to delete; |
| 3 | press 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 3 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, As mentioned above, 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 Insert | Name | Define, selecting the misapplied name and clicking Delete, and then renaming the range by selecting it and using Insert | Name | Define, 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.