5. External references

Instead of typing product descriptions, we are going to use a formula to fetch them from an external XML document. This document is XXE_install_dir/doc/spreadsheet/tutorial/products.html.

Click in cell C2 and use ToolsSpreadsheetInsert/Edit Formula to insert a new formula. Enter this multi-line formula:

location = "products.html#" & trim(B2) & "_desc"
=`document($location, .)`

First line is easy to understand. It assigns to local variable location a string built using the code of the product: "products.html#xe-1u_desc". "product.html" is the URL, relative to the location of the document being edited, of the external XML document containing product descriptions. "xe-1u_desc" is the ID of the element containing the description of product xe-1u.

Second line contains an XPath escape. The expression between backquotes '`' , which uses standard XPath function document(), allows to fetch a node found in an external document. Without the "#xe-1u_desc" fragment identifier, the whole document node is fetched. With the "#xe-1u_desc" fragment identifier, we fetch the element node having xe-1u_desc as its ID.

Copy the new formula to the clipboard (Ctrl+C) and paste it (Ctrl+V) into C3, C4, C5.