3.5.1 References

To compute fields in the table from other fields, formulas must reference other fields or ranges. In Org, fields can be referenced by name, by absolute coordinates, and by relative coordinates. To find out what the coordinates of a field are, press C-c ? in that field, or press C-c } to toggle the display of a grid.

Field references

Formulas can reference the value of another field in two ways. Like in any other spreadsheet, you may reference fields with a letter/number combination like ‘B3’, meaning the second field in the third row. However, Org prefers to use another, more general representation that looks like this:¹⁸

@ROW$COLUMN

Column specifications can be absolute like ‘$1’, ‘$2’, …, ‘$N’, or relative to the current column, i.e., the column of the field which is being computed, like ‘$+1’ or ‘$-2’. ‘$<’ and ‘$>’ are immutable references to the first and last column, respectively, and you can use ‘$>>>’ to indicate the third column from the right.

The row specification only counts data lines and ignores horizontal separator lines, or “hlines”. Like with columns, you can use absolute row numbers ‘@1’, ‘@2’, …, ‘@N’, and row numbers relative to the current row like ‘@+3’ or ‘@-1’. ‘@<’ and ‘@>’ are immutable references the first and last row in the table, respectively. You may also specify the row relative to one of the hlines: ‘@I’ refers to the first hline, ‘@II’ to the second, etc. ‘@-I’ refers to the first such line above the current line, ‘@+I’ to the first such line below the current line. You can also write ‘@III+2’ which is the second data line after the third hline in the table.

‘@0’ and ‘$0’ refer to the current row and column, respectively, i.e., to the row/column for the field being computed. Also, if you omit either the column or the row part of the reference, the current row/column is implied.

Org’s references with unsigned numbers are fixed references in the sense that if you use the same reference in the formula for two different fields, the same field is referenced each time. Org’s references with signed numbers are floating references because the same reference operator can reference different fields depending on the field being calculated by the formula.

Here are a few examples:

Range references

You may reference a rectangular range of fields by specifying two field references connected by two dots ‘..’. The ends are included in the range. If both fields are in the current row, you may simply use ‘$2..$7’, but if at least one field is in a different row, you need to use the general ‘@ROW$COLUMN’ format at least for the first field, i.e., the reference must start with ‘@’ in order to be interpreted correctly. Examples:

Range references return a vector of values that can be fed into Calc vector functions. Empty fields in ranges are normally suppressed, so that the vector contains only the non-empty fields. For other options with the mode switches ‘E’, ‘N’ and examples, see Formula syntax for Calc.

Field coordinates in formulas

One of the very first actions during evaluation of Calc formulas and Lisp formulas is to substitute ‘@#’ and ‘$#’ in the formula with the row or column number of the field where the current result will go to. The traditional Lisp formula equivalents are org-table-current-dline and org-table-current-column. Examples:

‘if(@# % 2, $#, string(""))’

Insert column number on odd rows, set field to empty on even rows.

‘$2 = '(identity remote(FOO, @@#$1))’

Copy text or values of each row of column 1 of the table named FOO into column 2 of the current table.

‘@3 = 2 * remote(FOO, @1$$#)’

Insert the doubled value of each column of row 1 of the table named FOO into row 3 of the current table.

For the second and third examples, table FOO must have at least as many rows or columns as the current table. Note that this is inefficient¹⁹ for large number of rows.

Named references

‘$name’ is interpreted as the name of a column, parameter or constant. Constants are defined globally through the variable org-table-formula-constants, and locally—for the file—through a line like this example:

#+CONSTANTS: c=299792458. pi=3.14 eps=2.4e-6

Also, properties (see Properties and Columns) can be used as constants in table formulas: for a property ‘Xyz’ use the name ‘$PROP_Xyz’, and the property will be searched in the current outline entry and in the hierarchy above it. If you have the ‘constants.el’ package, it will also be used to resolve constants, including natural constants like ‘$h’ for Planck’s constant, and units like ‘$km’ for kilometers²⁰. Column names and parameters can be specified in special table lines. These are described below, see Advanced features. All names must start with a letter, and further consist of letters and numbers.

Remote references

You may also reference constants, fields and ranges from a different table, either in the current file or even in a different file. The syntax is

remote(NAME,REF)

where NAME can be the name of a table in the current file as set by a ‘#+NAME:’ line before the table. It can also be the ID of an entry, even in a different file, and the reference then refers to the first table in that entry. REF is an absolute field or range reference as described above for example ‘@3$3’ or ‘$somename’, valid in the referenced table.

When NAME has the format ‘@ROW$COLUMN’, it is substituted with the name or ID found in this field of the current table. For example ‘remote($1, @@>$2)’ ⇒ ‘remote(year_2013, @@>$1)’. The format ‘B3’ is not supported because it can not be distinguished from a plain table name or ID.