## 45. Copy and Move FormulasBEFORE YOU BEGIN 44 About Calc Formulas SEE ALSO 50 Create a Range You can copy, move, and paste one cell into another using standard copy-and-paste tools such as the Windows Clipboard. When you copy formulas that contain cell addresses, Calc updates the cell references so they become relative references . For example, suppose that you enter this formula in cell A1 : KEY TERM Relative reference A cell that is referenced in relation to the current cell. ` =A2 + A3 ` This formula contains two cell references. The references are relative because the references A2 and A3 change if you copy the formula elsewhere. If you copy the formula to cell B5 , for example, B5 holds this: =B6 + B7 45. Copy and Move Formulas The original relative references update to reflect the formula's copied location. Of course, A1 still holds its original contents, but the copied cell at B5 holds the same formula referencing B5 rather than A1 . A dollar sign ($) always precedes an absolute reference . The reference $B$5 is an absolute reference. If you want to sum two columns of data ( A1 with B1, A2 with B2 , and so on) and then multiply each sum by some constant number, for example, the constant number can be a cell referred to as an absolute reference . That formula might resemble this: =(A1 + B1) * $J KEY TERM Absolute reference A cell reference that does not change if you copy the formula elsewhere. TIP When entering cell references, the letters are not case-sensitive. You can type a3 and Calc will convert it to the cell reference A3 . In this case, $J$1 is an absolute reference, but A1 and B1 are relative. If you copy the formula down one row, the formula changes to this: =(A2 + B2) * $J Notice that the first two cells changed because when you originally entered them, they were relative cell references. You told Calc, by placing dollar signs in front of the absolute cell reference's row and column references, not to change that reference when you copy the formula elsewhere. $B5 is a partial absolute cell reference. If you copy a formula with $B5 inside the computation, the $B keeps the B column intact, but the fifth row updates to the row location of the target cell. For example, if you type the formula ` =2 * $B5 ` in cell A1 and then copy the formula to cell F6 , cell F6 holds this formula: =2 * $B10 You copied the formula to a cell five rows and five columns over in the worksheet. Calc did not update the column name , B , because you told Calc to keep that column name absolute. (It is always B no matter where you copy the formula.) Calc added five to the row number, however, because the row number is relative and open to change whenever you copy the formula. TIP Most of the time, you'll use relative referencing. If you insert or delete rows, columns, or cells, your formulas remain accurate because the cells that they reference change as your worksheet changes.
NOTE You can make only the row or only the column of a cell address absolute. In the cell reference M$15 , the column named M is relative and will change if you copy a cell that contains this reference elsewhere, but the absolute row number, $15 , will not change. |

Sams Teach Yourself OpenOffice.org 2, Firefox and Thunderbird for Windows All in One

ISBN: 0672328089

EAN: 2147483647

EAN: 2147483647

Year: 2005

Pages: 232

Pages: 232

Authors: Greg Perry

flylib.com © 2008-2017.

If you may any questions please contact us: flylib@qtcs.net

If you may any questions please contact us: flylib@qtcs.net