If you start with a relative address such as A1, the first time you press this key combination, both row and column are set to absolute references ($A$1).

However, again if you move the formula it does not relocate.LibreOffice can convert the current reference, in which the cursor is positioned in the input line, from relative to absolute and vice versa by pressing F4. If you copy this to Sheet2.A1 you will see the formula =Sheet3.A2+$Sheet2.A2, that is the relative sheet reference also relocates. So if you are on Sheet1 and have a formula =Sheet2.A2+$Sheet2.A2 in A1. The $ prefix on sheet numbers is as Villeroy says Calc specific. In move operations relative addresses are not relocated.

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. 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 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.

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.

'Copying' can be done by one of two methods This works also with selected portions of a formula while in edit mode. Tip: Shift+F4 toggles the active cell's references A1->$A$1->A$1->$A1 and back to A1. Moving (Cut&Paste) references rather than copy does not adjust relative references. In other words: The $ 'freezes' the following row or column.

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. The $ creates an absolute reference, so $B is always specifically column B. Rarely used and Calc specific: Sheet2.A1 vs.

$A1 refers to the cell one row above in column A.Ī$1 refers to the cell in row #1 and one column to the left. This is not really Calc specific.Ī1 refers to the cell one row above and one column to the left.Ĭopying up: A#REF! Ĭopying to the left: #REF!1 Short tutorial on relative, mixed and absolute references in a spreadsheet.

Openoffice calc absolute reference