Page 349 - Excel 2013 All-in-One For Dummies

Range Names in Formulas 331
need to choose the Apply Names option from the Name a Range button’s drop-down menu or press Alt+MMA.
When you choose this command, Excel opens the Apply Names dialog box, where you select the range names that you want applied in formulas used in your worksheet by selecting the names in the Apply Names list box.
Note that when you first open this dialog box, it contains just two check boxes: Ignore Relative/Absolute and Use Row and Column Names (both of which are selected). You can click the Options button to expand the Apply Names dialog box and display other options that you can use when applying your range names, shown in Figure 1-16. The Apply Names options include the following:
✦ IgnoreRelative/Absolutecheckbox:Theprogramreplacescellreferences with the names that you’ve selected in the Apply Names list box, regard- less of the type of reference used in their formulas. If you want Excel to replace only those cell references that use the same type of references as are used in your names (absolute for absolute, mixed for mixed, and relative for relative), deselect this check box. Most of the time, you’ll want to leave this check box selected because Excel automatically assigns absolute cell references to the names that you define and relative cell references to the formulas that you build.
✦ Use Row and Column Names check box: The names created from row and column headings with the Create Names command appear in your formulas. Deselect this option if you don’t want these row and column names to appear in the formulas in your worksheet.
✦ Omit Column Name If Same Column check box: This prevents Excel from repeating the column name when the formula is in the same column. Deselect this check box when you want the program to display the column name even in formulas in the same column as the heading used to create the column name.
✦ Omit Row Name If Same Row check box: This prevents Excel from repeating the row name when the formula is in the same row. Deselect this check box when 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: Choose the Row Column option button (the default) if you want the row name to precede the column name when both names are displayed in the formulas, or choose the Column Row option button if you want the column name to precede the row name.
Book III Chapter 1
Building Basic Formulas