- Worldstart's Tech Tips And Computer Help - http://www.worldstart.com -

OpenOffice.org Calc: Relative vs Absolute References

Posted By April On June 15, 2011 @ 3:16 PM In OpenOffice | Comments Disabled

When you create a formula in a Calc spreadsheet you use cell locations in the formula to tell Calc where to find the data to use.

Take the following example:

In cell A7 I told Calc to add the values in cells A2 & A3.

[1]

Based on the way they’re written they are relative cell references… meaning that the way Calc understands the references, is to add the values in the cells that are 5 and 4 cells above the formula.

If we copy and paste that formula into cell A9 we would now see this:

[2]

Since we moved the formula 2 rows down the cells it pulls from are also 2 rows down… meaning that the actual numbers added are still exactly 5 and 4 cells above the formula.

When the formula is pasted into cell B7 we get this result:

[3]

It still adds the values that are exactly 5 and 4 cells above the formula but since it’s now in column B it’s adding the cells in column B too.

There is a way to reference a cell so that no matter how many times or where you copy / paste it, the data in the same cells will always be used.

This is called an absolute cell reference and to create one you simply have to use a strategically placed $ or two.

Each cell location is made up of 2 parts – the column letter and the row number.

If you place a $ in front of the column letter then no matter where the formula is moved it will always pull data from that column… however, the rows will still be treated as relative. You will find that the row numbers will change as appropriate even though the column letter didn’t.

=$A2+$A3

Placing a $ in front of the row number will hold it constant as the formula is pasted but the column will change.

=A$2+A$3

And, as you may have already guessed, inserting two $‘s (in front of both the column letter and the row number) will hold that cell reference as is no matter where you paste the formula. It will Absolutely reference that cell – end of story.

=$A$2+$A$3

Oh, and one more thing… you don’t actually have to hand-type all those dollar signs into your formulas.

Simply select a cell reference within a formula and use Shift + F4 to rotate between the four possible settings. (no $, $ on column letter only, $ on row number only or $ on both)

Understanding the power of the the $ in your formula references is an invaluable tool that will prevent a lot of frustration when formulas are moved or copied elsewhere.

~ April

 


Article printed from Worldstart's Tech Tips And Computer Help: http://www.worldstart.com

URL to article: http://www.worldstart.com/openoffice-org-calc-relative-vs-absolute-references/

URLs in this post:

[1] Image: http://www.worldstart.com/openoffice-org-calc-relative-vs-absolute-references/oooc_rel_cell_ref_ex/

[2] Image: http://www.worldstart.com/openoffice-org-calc-relative-vs-absolute-references/oooc_copypasted_rel_cell_ref_ex/

[3] Image: http://www.worldstart.com/openoffice-org-calc-relative-vs-absolute-references/oooc_pastedright_rel_cell_ref_ex/