4. Absolute references

Use the Attribute tool to add an ID to the table. Select the table using the node path bar, click on the line starting with id in the Attribute tool and type invoice_table, then press Enter.

Add a paragraph containing sentence "The VAT rate is the VAT rate of France.". After the "The VAT rate ", use ToolsSpreadsheetInsert/Edit Formula to insert a new formula.

Try to specify this formula as ="invoice_table"!D7. This does not work.

Outside a table, relative references are not allowed. Now specify ="invoice_table"!$D$7.

It works because we have used absolute references but this has deleted everything after "The VAT rate ". This is normal. Remember that the value computed by a formula is added just after the xxe-formula processing instruction. If there is already some text just after the xxe-formula processing instruction, this text is replaced.

Click on the formula to select it. Use SelectExtend Selection to Following Sibling (EscRight-Arrow) and then EditConvert (Ctrl+T) to convert the two selected nodes to a span.

Now press Insert to insert a new text node after the newly created span and type once again " is the VAT rate of France.".

What you have learned here is that, unless a formula is inserted in a table cell which contains nothing else, you'll almost always have to wrap it in its own element (typically span for XHTML and phrase for DocBook).

4.1. Better absolute references

Using ="invoice_table"!$D$7 to copy the content of cell D7 is not a good idea. Adding and removing purchased products to the table would change the last sentence to something that does not make sense.

Formulas can very easily reference elements by their ID and that's what we are going to do. Click on cell D7 and specify an attribute id for it. We have already done that for the table. This time, specify VAT as the value of attribute id of td D7.

Now double click on the formula contained in last sentence and, using the Formula Editor, change ="invoice_table"!$D$7 to =$(VAT).

Notice that this time, the end of the sentence, "is the VAT rate of France.", has not disappeared.

We are going to double-check this by manually triggering a full calculation of the spreadsheet. Use ToolsSpreadsheetUpdate for that.

[Note]About automatic calculation of the spreadsheet

By default, the spreadsheet engine is in auto-update mode.

In auto-update mode, a full calculation is automatically performed, if needed to, when the editing context changes. For example: type some text in a paragraph, then click in (or tab to) another paragraph to trigger a spreadsheet calculation.

In manual update mode, only newly inserted formulas are computed. To force a full calculation, the user has to explicitly use ToolsSpreadsheetUpdate.

Note that in both modes, a full calculation is automatically performed, if needed to, before validating or saving the document.

Using manual update mode is recommended if you have a slow computer or if you have inserted a lot of formulas in your document or if your formulas access many external documents (more about this in next section).