Hack 44 Identify Named Ranges on a Worksheet

   

figs/beginner.gif figs/hack44.gif

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
figs/exhk_0311.gif

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
figs/exhk_0312.gif


Excel Hacks
Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets
ISBN: 0596528345
EAN: 2147483647
Year: 2003
Pages: 136

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net