[Tutorial] Absolute, relative and mixed references

[Villeroy]

Short tutorial on relative, mixed and absolute references in a spreadsheet. This is not really Calc specific.

When used in cell B2:
A1 refers to the cell one row above and one column to the left.
Copying down: A2, A3, A4, ...
Copying to the right: B1, C1, D1, ...
Copying up: A#REF! [A is valid but there is no further row above row above A1]
Copying to the left: #REF!1 [Row 1 is valid but there is no further column left of A1]

$A1 refers to the cell one row above in column A.
Copying down: $A2, $A3, $A4, ...
Copying to the right: remains $A1

A$1 refers to the cell in row #1 and one column to the left.
Copying down: remains A$1
Copying to the right: B$1, C$1, D$1, ...

$A$1 refers to the cell A1
Copying down: remains $A$1
Copying to the right: $A$1

Same with A1:B5, A1:$B5, A1:B$5,...
Rarely used and Calc specific: Sheet2.A1 vs. $Sheet2.A1 when copied across sheets.

The $ creates an absolute reference, so $B is always specifically column B.

Without that $, you have a relative reference, meaning a position relative to whatever the current position is, so it changes for each cell where the reference is placed.
In other words: The $ "freezes" the following row or column.

Moving (Cut&Paste) references rather than copy does not adjust relative references.

Tip: Shift+F4 toggles the active cell's references A1-->$A$1-->A$1-->$A1 and back to A1. This works also with selected portions of a formula while in edit mode.

[TerryE]

"Copying" can be done by one of two methods

• if you edit->copy then paste say to an offset of (x,y) from a cell then any relative elements (row or column references) will be relocated accordingly.
• If you select the cell autofill (by positioning the mouse cursor on the bottom right of the cell or a cell range (so that the cursor changes from an arrow to a cross cursor) then autofill relocates relative cells accordingly. By example if B2 contains =B$1*$A2 then using the plus cursor to drag the fill area over B2:M13 will produce the multiplication table of the header row A:A and the header column 1:1
  If you use cut / paste or if you position on the centre of a cell and hold down the left button and start to drag the cells to a new location, the a little box appears at the tail of the mouse arrow, denoting a move operation. In move operations relative addresses are not relocated.

The $ prefix on sheet numbers is as Villeroy says Calc specific. So if you are on Sheet1 and have a formula =Sheet2.A2+$Sheet2.A2 in A1. If you copy this to Sheet2.A1 you will see the formula =Sheet3.A2+$Sheet2.A2, that is the relative sheet reference also relocates. However, again if you move the formula it does not relocate.

Have a play.

[Lanser]

How do I stop the reference changing when the referred to cell is moved?
Example:
on Sheet 2 I have the following formula part of one that I'd like to use to keep a 13 week rolling total with each new item added at the top of the list on Sheet 1

A3=$Sheet1.$A$4 A4=SUM($Sheet1.$F$4:$F$8)/5

If I go to sheet 1 and insert a row above 4 the reference changes to

A3=$Sheet1.$A$5 A4=SUM($Sheet1.$F$5:$F$9)/5

I thought the idea of an absolute ref was to fix it to that location not that cell is there something I'm going wrong or is there a way around this limitation?

[Villeroy]

I thought the idea of an absolute ref was to fix it to that location not that cell is there something I'm going wrong or is there a way around this limitation?

Relative addressing makes a difference when you copy referring cells. However, when you move the referred cells all references in the referring cells adjust to the new position of your data. Insertion/removal of cells moves the existing cells so references, in the referring cells will adjust. I believe this is what most users expect in most cases.
You can implement another behaviour by fomulas as described here
viewtopic.php?f=32&t=2944

[huw]

In a document called "Records file" how do I adjust the following formula and its like

Code: Select all   Expand viewCollapse view

=IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";'Data page'.$B$1:$B$12000;0);"")

so that when I use Move/Copy Sheet... to send the sheet to a new document, the formulas carry on working, referring to the sheet called "Data page" in the original document (which I'm aware will become linked using a hidden sheet)? I assume I have to expand on the 'Data page' component of the reference, but I can't figure it out. If I move it without making adjustments first then I get

Code: Select all   Expand viewCollapse view

=IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";#REF!.$B$1:$B$12000;0);"")

[Villeroy]

Scenario with absolute reference to $DataSheet
Source document:
DataSheet Sheet1 Sheet2 ResultSheet
Resultsheet.A1 =$DataSheet.$X$99

Target document:
Sheet1 Sheet2 Sheet3 Sheet4 Copied_ResultSheet
Copied_Resultsheet.A1 =$Sheet1.$X$99
refers to the first sheet, just as in the source document


Scenario with relative reference to DataSheet
Source document:
DataSheet Sheet1 Sheet2 ResultSheet
Resultsheet.A1 =DataSheet.$X$99

Target document:
Sheet1 Sheet2 Sheet3 Sheet4 Copied_ResultSheet
Copied_Resultsheet.A1 =Sheet2.$X$99
refers to the third predecessor, just as in the source document

[huw]

So in "Records file", on the sheet to move:

Find 'Data page' & Replace All with $'Data page'

which makes the example:

Code: Select all   Expand viewCollapse view

=IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";$'Data page'.$B$1:$B$12000;0);"")

Then I move the sheet to a new document, which gives me a warning "The new table contains absolute references to other tables which may be incorrect!" and this time the formula remains the same.

In the new document:

• Insert sheet from file
• Select "Records file"
• Turn ON Link
• Select "Data page"
• Hide sheet once it is in

Yes, that's working. Thanks for the clue.

Edit: clarification.

[Villeroy]

With relative sheet references I can think of another method:
Move 'Records file' next to 'Data Sheet' within the source document before you copy 'Data Sheet' next to the respective sheet in the other file.

[Jason1979]

I know this thread is old, but this could help others that come across it.
I have a Worksheet in file A that references a worksheet called "Receipts"
When I copy and move this worksheet to File B I have a worksheet in this file also called "Receipts"
I want to make sure that the Worksheet once moved into File B still references "Receipts", my issue was that "Receipts" in File B was not able to be in the same relative position and despite using the $Receipts. the Worksheet Name would change.

My Solution: To Use Indirect

To help illustrate let's say my formula before was =sum($Receipts.A1:A100)

My formula in the Worksheet is no longer using the $Receipts. Instead I have put a cell (B2) in the worksheet and filled it with "Receipts" Now, my formula uses =sum(indirect($B$2&".A1:A100")) Now when I copy, since B2 is still Receipts, the formula will continue to use the worksheet called Receipts regardless of it's position in the File B

Hope this is also useful to others!