Microsoft Office Tutorials and References
In Depth Information
Using Names
Applying names to existing references
When you create a 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:
=A1–A2
If you define the names Income for A1 and Expenses for A2, Excel doesn’t automatically change
your formula to
=Income-Expenses
However, replacing cell or range references with their corresponding names is fairly easy. Start
by selecting the range that contains the formulas that you want to modify. Then choose
Formulas➜Defined Names➜Define Name➜Apply Names. 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. Hidden names exist in a workbook but
don’t appear in the Name Manager 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 the workbook:
Sub DeleteHiddenNames()
Dim n As Name
Dim Count As Integer
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
n.Delete
Count = Count + 1
End If
Next n
MsgBox Count & “ hidden names were deleted.”
End Sub
 
Search JabSto ::




Custom Search