[Tutorial] Absolute, relative and mixed references

edit this topic's initial post · by **Villeroy** » 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.

by **TerryE** » 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.

by **Lanser** » 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?

edit this topic's initial post · by **Villeroy** » 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

by **huw** » 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 view Collapse 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 view Collapse view: =IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";#REF!.$B$1:$B$12000;0);"")

edit this topic's initial post · by **Villeroy** » 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

by **huw** » 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 view Collapse 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.

edit this topic's initial post · by **Villeroy** » 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.

by **Jason1979** » 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!

[Tutorial] Absolute, relative and mixed references

[Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Re: [Tutorial] Absolute, relative and mixed references

Who is online