[Tutorial] Absolute, relative and mixed references

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Tutorial] Absolute, relative and mixed references

Post by 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.
Last edited by Villeroy on Sat Aug 16, 2008 4:17 pm, edited 1 time in total.
Reason: Added #REF! examples
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: [Tutorial] Absolute, relative and mixed references

Post by 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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Lanser
Posts: 1
Joined: Tue Mar 18, 2008 10:39 am

Re: [Tutorial] Absolute, relative and mixed references

Post by 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?
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Absolute, relative and mixed references

Post by 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
http://user.services.openoffice.org/en/ ... =32&t=2944
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: [Tutorial] Absolute, relative and mixed references

Post by huw »

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

Code: Select all

=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

=IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";#REF!.$B$1:$B$12000;0);"")
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Absolute, relative and mixed references

Post by 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
huw
Volunteer
Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

Re: [Tutorial] Absolute, relative and mixed references

Post by 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

=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.
Last edited by huw on Fri Apr 18, 2008 9:41 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Tutorial] Absolute, relative and mixed references

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Jason1979
Posts: 6
Joined: Thu Aug 16, 2012 9:05 pm

Re: [Tutorial] Absolute, relative and mixed references

Post by 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!
OpenOffice 3.x installed on Win 7 Home
Post Reply