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

326 Range Names in Formulas
When you preface a range name with the sheet name as shown in this exam- ple, you don’t have to use the sheet name part of the range name in the for- mulas that you create on the same worksheet. In other words, if you create a SUM formula that totals the values in the ‘Inc. Statement 04’!Costs range somewhere on the Inc. Statement 04 worksheet, you can enter the formulas as follows:
=SUM(Costs)
However, if you were to create this formula on any other worksheet in the workbook, you would have to include the full range name in the formula, as in
=SUM(‘Inc. Statement 04’!Costs)
Naming constants and formulas
In addition to naming cells in your worksheet, you can also assign range names to the constants and formulas that you use often. For example, if you are creating a spreadsheet table that calculates sales prices, you can assign the discount percentage rate to the range name discount_rate. Then, you can supply this range name as a constant in any formula that calculates the sale discount used in determining the sale price for merchandise.
For example, to assign a constant value of 15% to the range name discount_ rate, you open the New Name dialog box and then type discount_rate in
the Name text box and =15% as the discount rate in the Refers To text box before selecting OK. After assigning this constant percentage rate to the range name discount_rate in this manner, you can apply it to any formula by typing or pasting in the name (see the “Using names in building formulas” section that follows in this chapter for details).
In addition to naming constants, you can also give a range name to a formula that you use repeatedly. When building a formula in the Refers To text box of the New Name dialog box (Alt+MMD), keep in mind that Excel automatically applies absolute references to any cells that you point to in the worksheet. If you want to create a formula with relative cell references that Excel adjusts when you enter or paste the range name in a new cell, you must press F4 to convert the current cell reference to relative or type in the cell address with- out dollar signs.
When creating the constant in the New Name dialog box, don’t change the Scope setting from Workbook to a particular sheet in the workbook unless you’re positive that you’ll never need to use that constant in a formula on any other worksheet. If you limit the scope to a particular worksheet, Excel