List of Names
A list of Names in the workbook is produced by the AutoDocumentation List Names command. The list is on sheet X~Names. If it exists, the list of sheets on sheet X~Sheets will be updated when this command is run.
This listing is particularly useful as it gives access to information that is not otherwise readily available. The Define Name dialog, accessed through Insert > Name > Define... shows only names defined globally or on the current worksheet, as does the Name Box. In addition, the Name Box shows only valid Names that refer directly to ranges, omitting names that refer to formulae.
Sheet X~Names shows all names, whether they are global or local to a worksheet, valid or invalid, and whether they refer to ranges or formulae.
The contents of the list are as follows:
- Name
- The name of the Name. (Admittedly a confusing concept.)
- If the Name refers to a range, the entry in this column is a hyperlink: clicking on it will take you to the range that the Name refers to, unless the range is on a sheet that is currently hidden.
- Local to sheet
- If the Name is defined to be local to a worksheet, the name of that worksheet, otherwise blank.
- Refers to
- What the Name refers to. This is the definition that is seen in the Define Name dialog.
- Value
- If the Name refers to a single cell, the value of that cell. If the Name refers to a range through a formula, the address of the range.
- Range
- Whether the Name refers to a range or not, according to Excel. Excel recognises that a Name refers to a range when it is defined as an absolute cell address, or a formula that returns a range. It does not recognise a relative cell address as a range. Ranges in other workbooks are recognised only if the source workbook is open.
- Visible
- Whether the Name is visible or not. Names that are not visible are usually names defined by Excel, such as _FilterDatabase which is used by AutoFiltering.
- Valid
- Whether the Name is valid. A Name can become invalid if the range to which it refers is removed, for example.
- External
- Whether the name refers to another workbook, ie contains an external link.
Updating the list
The list of names can be updated at any time by using the command XLSior > AutoDocument > List Names.
Sorting the list
The order in which the names appear in the list is determined by your user and workbook preferences.