Applying names to existing references

When you create a new name for a cell or a range, Excel doesn't automatically use the name in place of existing references in your formulas. For example, assume that you have the following formula in cell F10:

If you define the names Income for A1 and Expenses for A2, Excel won't automatically change your formula to =Income-Expenses. It's fairly easy to replace cell or range references with their corresponding names, however. Start by selecting the range that you want to modify. Then choose the Insert ^ Name ^ Apply command. In the Apply Names dialog box, select the names that you want to apply and then click OK. Excel replaces the range references with the names in the selected cells.

Hidden Names

Some Excel macros and add-ins create hidden names. These are names that exist in a workbook but don't appear in the Define Name dialog box. For example, the Solver add-in creates a number of hidden names. Normally, you can just ignore these hidden names. However, sometimes these hidden names create a problem. If you copy a sheet to another workbook, the hidden names are also copied, and they might create a link that is very difficult to track down.

You can use the following VBA procedure to delete all hidden names in a workbook:

Sub DeleteHiddenNames() Dim n As Name Dim Count As Integer For Each n In ActiveWorkbook.Names If Not n.Visible Then n.Delete

MsgBox Count & " hidden names were deleted." End Sub

Unfortunately, there is no way to automatically unapply names. In other words, if a formula uses a name, you can't convert the name to an actual cell or range reference. Even worse,if you delete a name that is used in a formula, the formula does not revert to the cell or range address — it simply returns a #NAME? error.

My Power Utility Pak add-in (available on the companion CD-ROM) includes a utility that scans all formulas in a selection and automatically replaces names with their cell addresses.

Was this article helpful?

0 0

Post a comment