Dan Butler's
TNPC Newsletter

Annoyances Alley - More About Excel Range Names (Create Names)

by T. J. Lee

Annoyances Alley is a tiny glimpse into the Annoyances series of Office 97 related books from Woody Leonhard, Lee Hudspeth, and T.J. Lee. This is just one of the detailed tips related to optimization and customization that appears in "Excel 97 Annoyances" published by O'Reilly & Associates (ISBN 1-56592-309- X) and reprinted herewith permission.

"Create Names

Excel has some nice features that make creating names a snap. If you highlight a cell, or a range of cells, you can ask Excel to create names for you based on any labels in certain locations within the selection.

The table in this example was first selected. Then we clicked on Insert / Names / Create and got the dialog box shown in Figure 4- 45. Excel has done some guesswork and offers to use the top row and left column as range names for the selected cells. If these defaults are accepted and OK clicked, the range names shown in Table 4-4 are created.

Note in this case that both column and row ranges were named simultaneously, and that the cells containing the labels are not part of the defined Refers to range. Because we knew that Excel does not like spaces or names that begin with numerals, we stacked the deck in our favor by the way we named our columns, i.e., Qtr.1, with the text prefix "Qtr" first, and a period instead of a space between the text and the number.

Had we used something perhaps more typical, like "1st Qtr", where a number is first and a space is in the label, Excel would have adjusted by changing the label in the associated range name. "1st Qtr" would have become the name "_1st_Qtr" with a leading underscore and an underscore replacing the space. This is actually a good thing, so don't let it annoy you the first time you run into it."

Annoyances titles are available for order at the Office Annoyances Web site:
http://www.PRIMEConsulting.com/annoyances/?v2.10
or at the publisher's site:
http://www.ora.com/annoyed/