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

334 Controlling Formula Recalculation
✦ Select the cell with the constant or formula that you want to bring for- ward to a new location and then click the Copy button in the Clipboard group on the Ribbon’s Home tab or press Ctrl+C. Then click the cell where the linking formula is to appear before you choose the Paste Link option from the Paste button’s drop-down menu.
When you use the first simple formula method to create a link, Excel uses
a relative cell reference to refer to the cell containing the original value or formula (as in =A10 when referring to an entry in cell A10). However, when you use the second copy-and-paste link method, Excel uses an absolute cell reference to refer to the original cell (as in =$A$10 when referring to an entry in cell A10).
When you create a linking formula to a cell on a different sheet of the same workbook, Excel inserts the worksheet name (followed by an exclamation point) in front of the cell address. So, if you copy and paste a link to a for- mula in cell A10 on a different worksheet called Income 15, Excel inserts the following linking formula:
=’Income 15’!$A$10
When you create a linking formula to a cell in a different workbook, Excel inserts the workbook filename enclosed in square brackets before the name of the worksheet, which precedes the cell address. So, if you bring forward a formula in cell A10 on a worksheet called Cost Analysis in the Projected Income 16 workbook, Excel inserts this linking formula:
=’[Projected Income 16.xls]Cost Analysis’!$A$10
If you ever need to sever a link between the cell containing the original value or formula and the cell to which it’s been brought forward, you can do so
by editing the linking formula. Press F2, then immediately recalculate the formula by pressing F9, and then click the Enter button on the Formula bar or press Enter. This replaces the linking formula with the currently calcu- lated result. Because you’ve converted the dynamic formula into a constant, changes to the original cell no longer affect the one to which it was originally brought forward.
Controlling Formula Recalculation
Normally, Excel recalculates your worksheet automatically as soon you change any entries, formulas, or names on which your formulas depend. This system works fine as long as the worksheet is not too large or doesn’t contain tables whose formulas depend on several values.