Page 161 - Excel Timesaving Techniques for Dummies

30_574272 ch26.qxd 10/1/04 10:48 PM Page 146
146
Technique 26: Using Range Names
The complete Apply Names options include the Figure 26-9 illustrates how the Apply Names feature
following: works. First, I created range names for the sales table
by selecting the range A2:E8, opening the Create
Ignore Relative/Absolute: Select this check box Name dialog box, and using the Top Row and Left
to replace cell references with the names that Column defaults (see “Creating names from row and
you’ve selected in the Apply Names list box column headings” earlier in this technique). Next, I
regardless of the type of reference used in their selected the cell range D3:E8, which contains all the
formulas. Clear this check box if you want to cells with formulas in this table, and then opened
replace only those cell references that use the the Apply Names dialog box (Insert➪Name➪Apply).
same type of references as your names (absolute
for absolute, mixed for mixed, and relative for
relative).
Most often, you want to leave this check box
selected because Excel automatically assigns
absolute cell references to the names that you
define and relative cell references in the formulas
that you build.
Use Row and Column Names: Select this check
box to have the names appear in your formula that
you created from row and column headings with
the Create Names feature. Clear this check box if
you don’t want these row and column names to
appear in the formulas in your worksheet.
• Figure 26-8: Applying range names to the selected
Omit Column Name if Same Column: Select this formulas in the table.
check box to prevent Excel from repeating the
column name when the formula is in the same
column. Clear this check box if you want the pro-
gram to display the column name even in formu-
las in the same column as the heading used to
create the column name.
Omit Row Name if Same Row: Select this check
box to prevent Excel from repeating the row name
when the formula is in the same row. Clear this
check box if you want the program to display the
row name even in formulas in the same row as
the heading used to create the row name.
Name Order: You have two choices here: • Figure 26-9: Worksheet table after assigning range
names to its formulas.
Row Column: Click this option button (the
default) to have the row name precede the Because I wanted the row headings included in
column name when both names are displayed
the formula’s range, I clicked the Options button
in the formulas.
to expand the Apply Names dialog box and then
Column Row: Click this option button to have cleared the Omit Row Name if Same Row check box
the column name precede the row name. and clicked OK. As a result, Excel inserted the code