Page 1 of 1

### [Tutorial] Absolute, relative and mixed references

Posted: Thu Feb 07, 2008 11:36 pm
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.

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Fri Feb 08, 2008 2:59 am
"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.

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Tue Mar 18, 2008 11:05 am
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?

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Tue Mar 18, 2008 11:23 am
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

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Thu Apr 17, 2008 5:10 pm
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);"")`

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Thu Apr 17, 2008 5:24 pm
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

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Thu Apr 17, 2008 6:47 pm
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.

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Thu Apr 17, 2008 8:04 pm
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.

### Re: [Tutorial] Absolute, relative and mixed references

Posted: Sat Dec 22, 2012 3:03 am
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!