5. References

5.1. Reference to a variable

There is nothing special to do to reference a variable, except that a variable needs to have been assigned a value before being referenced. Example:

x = 2
x = x + 1
= 2*x*x

5.2. Reference to an element having a specific ID

The syntax of this type of reference is: $(ID), where ID is the ID of a element contained in the same document as the formula. Example taken from the tutorial:

= left($(vat), len($(vat)) - 1)

This type of reference returns an XML nodeset containing a single element. If there is no element having specified ID, the reference returns the empty nodeset. More on this below.

5.3. Reference to table cells

The following references are said to be relative:

  • First cell of a table: A1.

  • Second row: 2:2.

  • Second column: B:B.

  • First two rows: 1:2.

  • First two columns A:B.

  • Four cells in the top/left corner of the table: A1:B2.

The following references are said to be absolute:

  • First cell of a table: $A$1.

  • Second row: $2:$2.

  • Second column: $B:$B.

  • First two rows: $1:$2.

  • First two columns $A:$B.

  • Four cells in the top/left corner of the table: $A$1:$B$2.

Mixed references are possible too: A$1, $A1, 2:$2, $2:2, etc.

What does this mean? Example: cell $A$3 (first column, third row) contains the following formula:

= A1 + A2

Because the formula uses relative references, XXE translates this to:

= $[-2,0] + $[-1,0]

which means add the cell which is 2 rows above me to the cell which is 1 row above me.

If you copy the formula of $A$3 to $B$3 (second column, third row), the copied formula will add $B$1 to $B$2 because the cell which is 2 rows above $B$3 is $B$1 and the cell which is 1 row above $B$3 is $B$2.

On the other hand, if cell $A$3 contained the following formula:

= $A$1 + $A$2

Because the formula uses absolute references, XXE would have translated this to:

= $[1,1] + $[1,2]

which means add the cell which is at first column, first row to the cell which is at first column, second row.

With absolute references, after copying the formula of $A$3 to $B$3, the copied formula would still add $A$1 to $A$2.

[Important]Important
  • Relative references are allowed only when the formula is inside a table cell.

  • References to cells, whether relative or absolute, are really possible when the XML document is displayed using a styled view.

    A reference such as $A$3 cannot be evaluated unless the formula has an ancestor element formatted as a table.

    A reference such as "Income"!$C$4 cannot be evaluated unless element with ID Income is formatted as a table.

5.3.1. The $[row,column] notation

This notation is the one which is internally used by XXE. You can type $[row,column] references if you want, but XXE will never show you these references as you typed them. XXE (the status bar, the formula editor, etc) will automatically display cell references using the customary A1 notation, which is much more readable.

Absolute reference examples: $A$1 = $[1,1] $2:$2 = $[2,] $B:$B = $[,2] $1:$2 = $[1,]:$[2,] $A:$B = $[,1]:$[,2] A1:B2 = $[1,1]:$[2,2]

Relative reference examples, the formula being inside cell $A$3: A1 = $[-2,0] 2:2 = $[-1,] B:B = $[,+1] 1:2 = $[-2,]:$[-1,] A:B = $[,0]:$[,+1] A1:B2 = $[-2,0]:$[-1,+1]

5.3.2. Reference to cells in another table of the document

The element formatted using CSS property "display:table;" must have an ID. The syntax is: "ID"!cell_reference. Examples: "Income"!$C$4, "table-23"!A:A (relative references are allowed too if the formula has itself an ancestor element formatted as a table).