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 here with permission.
REVIEWER'S CORNER... "Like William Safire's famous columns on English usage, these authors' tips reflect extensive experimentation and deep familiarity with how these products actually behave (as opposed to what their manuals say they'll do)." -- Peter Coffee, PC Week, reviewing "Excel 97 Annoyances"
"Range Names and Labels
Individual cells or groups of cells can be given text descriptions called names -- plain English descriptions made up of letters, numbers, periods, and underscore characters. If you are not using names it's high time you start. Take a look at Figure 4-41.
No cells were explicitly named in this example, yet we can use the names "Income" and "Expenses" in formulas within the sheet. This is because of the new Label feature in Excel 97 that lets you use what the Redmond PR machine calls "natural language" in your formulas. That's a new moniker for an old concept, the named range. But in this case it's Excel that is assigning the names automatically. Here's how it works.
Excel uses whatever labels are handy and applies those names to the appropriate rows and columns. The formulas in cells C4, D4, and C5 are all identical: =Income. Excel has applied the name "Income" to cells C2:E2. You won't find a named range if you look in the Insert / Name / Define, or in the Go To dialog, but you can use the name in a formula just the same. So the =Income in C4 returns 500, the value from cell C2. D4 returns the value 25, which it reads from cell D2; and the formula in E4 returns the string "Expense", the value from cell E2. Columns are also affected. If there were no value in C2 but you had a value in B4, the formula in C4 would return the value from B4. On row 2, the Income name stops where the Expense label appears in column E row 2. The formula in F4 then, =Expenses, returns 498 from row 2.
Confusing? More like annoying. The names that Excel is assigning don't show up in the defined names list so you can't check what name refers to which range. Overall, it's nice of Excel to go to all this trouble, but we strongly recommend you explicitly name your ranges so this issue of "What cell will it return, the row or the column?" does not arise.
In Figure 4-42 cell C2 has been named Income (Insert / Name / Define) and C3 has been named Expenses. This makes using the names in a formula very straightforward since there is only one cell each name could refer to.
A name can be up to 255 characters, can be made up of letters, numbers, periods and the underscore. It must begin with either a letter or underscore and cannot look like an actual cell reference (example: A1 or R1C1). You can use upper and lower case letters to make the name more readable, but case is ignored by Excel (i.e., you can't have Sales and SALES as two different names in the same sheet).
In the rest of the book, we'll use explicitly named ranges."
Annoyances titles are available for order at the Office
Annoyances Web site:
http://www.PRIMEConsulting.com/annoyances/
or at the publisher's site:
http://www.ora.com/annoyed/
