Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 44 Identify Named Ranges on a Worksheet

Excel enables users to give meaningful names to
specific ranges in their worksheets.
As the number of different named ranges on a worksheet grows, you will need tools for
identifying the areas referenced by your named ranges.

Here
are two quick methods you can use to identify the referenced ranges
for each named range.

Method 1

One very quick way to identify referenced
ranges is to select Insert Name Paste,
or press F3.
In the Paste Name dialog, click OK, as shown in Figure 3-11, and Excel will list all your names in rows,
starting from your active cell, with the names'
corresponding references in the opposite column.

Figure 3-11. The Paste Name dialog

This will give you a list of all your
names in the active workbook.

Although this can be handy to help you identify specific ranges, it
still requires that you either manually select the specific named
range, or perhaps use the Go To... dialog.
However, once you have a list of named ranges, you can
remove all the referenced cell addresses corresponding to the names
and replace them with a simple hyperlink function.

This will enable you to create a list of all named ranges. Clicking
any item on the list will take you to the specified range. For instance, assume your list of names
resides in column A, starting from cell A1.
In cell B1, enter this formula:

 =HYPERLINK("[Book1.xls]"&A1,A1)

Copy this formula down as far as you need to and replace
Book1.xls with your
workbook's name.

Method 2

This
method is very simple but not very well known.
It was highlighted by one of the OzGrid Excel Forum
members in the "Hey! That is Cool!"
section of the OzGrid.com web site (http://www.ozgrid.com/forum/forumdisplay.php?fid=14).

All you need to do is set the zoom on your Excel worksheet to any
percentage lower than 40i.e., 39% or less. This will display all your named ranges on
the sheet for easy identification, as shown in Figure 3-12.

Figure 3-12. Named range zoom providing easy identification of named ranges