Page 158 - Excel Timesaving Techniques for Dummies

30_574272 ch26.qxd 10/1/04 10:48 PM Page 143
Name That Range!
When assigning descriptive names to cell 143
ranges in the Define Name dialog box, you
never have to include the sheet name; just
make sure that the descriptive names are
unique. Likewise, when referring to range
names in formulas, don’t take time to add the
sheet reference because Excel keeps track of
this automatically.

Assigning range names that
span different sheets

• Figure 26-2: Getting ready to create range names for a The only time that sheet names are really required as
table by using its row and column headings.
part of the range name is when the cell range it refers
to spans different sheets of the workbook. In order to
name these so-called 3-D references (that is, the same
cell range that spans multiple adjacent worksheets),
you need to specify the different worksheets involved.

The easiest way to do this is by specifying the sheets
in the Define Name dialog box as follows:

1. Make the first worksheet included in the 3-D
reference active.
2. Choose Insert➪Name➪Define to open the
Define Name dialog box.
3. In the Names in Workbook text box, type the
descriptive name for the range that spans more
than one sheet.
• Figure 26-3: Worksheet after pasting a list of the names 4. Press the Tab key until the Refers To text box is
created with the Create Names feature. selected and then type = (equal sign).
5. Select the tab of the active sheet and then hold
As you can see from the range name list shown in
down the Shift key as you select the tab of the
Figure 26-3, when Excel assigns a range name, not last worksheet to be included.
only does the program use absolute cell references
to specify the cell range that the name references, When you select the tab of the active sheet, Excel
but it also prefaces the cell range with the sheet inserts its sheet reference in the Refers To text
name. So, for example, the Sales_Price range name box. When you Shift+click the tab of the last sheet
(referring to the cell range E3:E8) is listed at the bot- in the 3-D reference, Excel inserts its sheet refer-
tom of the range name table as ence after that of the active sheet — separated
by a colon.
Sheet1!$E$3:$E$8
6. Select the range of cells in the active sheet to be
included in all the sheets in the 3-D reference.
Note that the sheet name is always separated from
the cell range by an exclamation point.