[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.
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
[Tutorial] Absolute, relative and mixed references
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.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Tutorial] Absolute, relative and mixed references
"Copying" can be done by one of two methods
Have a play.
- 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.
Have a play.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
Re: [Tutorial] Absolute, relative and mixed references
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?
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
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.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?
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Tutorial] Absolute, relative and mixed references
In a document called "Records file" how do I adjust the following formula and its like
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&".*";'Data page'.$B$1:$B$12000;0);"")
Code: Select all
=IF(A2<>"";MATCH(".*"&A2&".*"&B2&".*";#REF!.$B$1:$B$12000;0);"")
Re: [Tutorial] Absolute, relative and mixed references
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Tutorial] Absolute, relative and mixed references
So in "Records file", on the sheet to move:
Find 'Data page' & Replace All with $'Data page'
which makes the example:
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:
Edit: clarification.
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);"")
In the new document:
- Insert sheet from file
- Select "Records file"
- Turn ON Link
- Select "Data page"
- Hide sheet once it is in
Edit: clarification.
Last edited by huw on Fri Apr 18, 2008 9:41 am, edited 1 time in total.
Re: [Tutorial] Absolute, relative and mixed references
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Tutorial] Absolute, relative and mixed references
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!
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