OpenOffice.Org 1.0 Resource Kit

Using Cell References

Cell references are cell addresses, such as A1, B4, and C5, entered into formulas. Cell references are what make spreadsheets so flexible, because as the values within cells change, the cell references stay the same: cell B4 is cell B4, whether the value inside it is 2 or (5*(4^3))/7.

When you build formulas with cell references, the formulas not only stay smaller and more manageable, but they also stay the same even when the contents of cells change.

Following are tips for using cell references.

Click, Don't Type

You don't have to type cell references manually into a formula. As you're building a formula, you can click cells and cell ranges in a spreadsheet to enter those cells and ranges as cell references, as shown in Figure 20-7.

Relative and Absolute Cell References

Figure 20-8 shows a quick look at the physical makeup of relative and absolute cell references before we begin the discussion of them.

Relative cell references

See Figure 20-9. When you select and cut or copy a group of cells that have a calculated value, then paste the cells into a new location, Calc changes the cell references in the formulas so that the calculated values remain intact.

This is possible because the cell references in the formulas are relative to the rest of the cells. No matter where the group of cells moves, they keep the same relation to the other cells, as Figure 20-9 illustrates.

To see how relative cell references differ in looks from absolute cell references, see the section on absolute cell references, next .

Absolute cell references

There may be times when using relative cell references doesn't work well; for example, when you're referencing cells whose locations will never change. These cells have an absolute position.

Absolute cell references come mainly into play when you're using Calc's automatic fill feature (see Filling to Increment Data on page 531), which increments values and cell references as you fill.

Sometimes you may not want cell references to increment. Using the Credit Card Calculator in the Guided Tour of Calc on page 500, there is a row of cells containing values that need to be referenced at all times, whose locations will never change, as shown in Figure 20-10.

To set up references to cells that aren't going to change locations, put a dollar sign in front of the column letter and row number. For example, see Figure 20-11.

In Figure 20-10, if you didn't use absolute cell references in the formulas in row 10, and you tried to do an automatic fill, the references to the cells in row 5 would increment from, for example, D5 to D6, from D6 to D7, and so on, throwing off your calculations. When you enter the absolute cell reference to $D$5, the cell reference stays pointed at cell D5 even when you use automatic fill.

You don't have to put a dollar sign in from of the column and the row. You can use different combinations of absolute/relative cell references, depending on how you're going to use automatic fill. As a general rule:

• If you're going use automatic fill to fill across rows (left and right), put a dollar sign before column letters .

• If you're going to use automatic fill up and down in columns , put the dollar sign in front of row numbers .

• If you're referencing cells that will never change location, put a dollar sign in front of the column letter and row number.

Note

Calc has a keyboard shortcut for setting absolute cell references. In a formula, when you highlight a cell reference, press Shift+F4 repeatedly to set the absolute cell reference combination you want, as shown in Figure 20-12.