Microsoft Office Tutorials and References
In Depth Information
returned. Also, many of the add-ins that ship with Excel create hidden names. (See the “Hidden names” side-
You can modify the reference for any of the names that Excel creates automatically, but make sure that you un-
derstand the consequences.
Some Excel macros and add-ins create hidden names. These names exist in a workbook, but don't appear in the
Name Manager dialog box or the Name 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 problems. If
you copy a sheet to another workbook, the hidden names are also copied, and they may create a link that is very
difficult to track down.
Although Excel's Name Manager is very versatile, it doesn't have an option to display hidden names. Here's a
simple VBA procedure that lists all hidden names in the active workbook. The macro adds a new worksheet, and
the list is written to that worksheet:
Dim n As Name, r As Long
r = 1
For Each n In ActiveWorkbook.Names
If Not n.Visible Then
Cells(r, 1) = n.Name
Cells(r, 2) = “'” & n.RefersTo
r = r + 1
Creating Multisheet Names
Names can extend into the third dimension; in other words, they can extend across multiple worksheets in a
workbook. You can't simply select the multisheet range and type a name in the Name box, however. You must
use the New Name dialog box to create a multisheet name. The syntax for a multisheet reference is the follow-
In Figure 3-8, a multisheet name, DataCube, defined for A1:E5, extends across Sheet1, Sheet2, and Sheet3.
You can, of course, simply type the multisheet range reference in the Refers To field. If you want to create the
name by pointing to the range, though, you'll find it a bit tricky. Even if you begin by selecting a multisheet
range, Excel does not use this selected range address in the New Name dialog box.