Microsoft Office Tutorials and References
In Depth Information
Working with formulas
applied also contained the formula =D5. But because cell I17 isn’t in the same row or
column as any of the defined ranges, applying names normally results in an error value.
Applying names works well in tables where your named ranges intersect, but to apply names in
cells outside the table, you can force row and column names to be included in formulas by
clearing the Omit Column Name If Same Column and Omit Row Name If Same Row check
Figure 12-19 Use the Apply Names dialog box to substitute names for cell and range references
in your formulas. Click Options to display all the options shown here.
The Name Order options control the order in which row and column components appear.
For example, if we apply names using the Column Row option, the formula in cell I17 in
Figure 12-19 becomes =Qtr_3 Region_2.
For more information about implicit intersection, see “Getting explicit about intersections”
later in this chapter.
Select the Ignore Relative/Absolute check box to replace references with names regardless
of the reference type. In general, leave this check box selected. Most name definitions use
absolute references (the default when you define and create names), and most formulas
use relative references (the default when you paste cell and range references in the formula
bar). If you clear this check box, absolute, relative, and mixed references are replaced with
name definitions only if the definitions use the same reference style.
Selecting the Use Row And Column Names check box is necessary if you want to apply
names in intersection cases, as we showed in the examples. If you define names for
individual cells, however, you can clear the Use Row And Column Names check box to apply
names to only specific cell references in formulas.