External Referencing Formulas
<c r="B2"> <f>[1]Sheet1!$A$1</f> <v>1</v> </c>
In the above example, cell B2 contains a formula that references a cell in another workbook, namely ‘C:\[ExternalBook.xlsx]Sheet1’!$A$1
. This formula is referencing ExternalBook.xlsx
located at c:\
. Furthermore, the formula is requesting the value of cell A1 on Sheet1
of that particular workbook.
Instead of writing ‘C:\[ExternalBook.xlsx]Sheet1’!$A$1
directly in the formula, it is desirable to make all external references much more accessible, especially given the potentially enormous size of a cell table. Therefore, the URL and file location is persisted using the relationships semantic, in a relationship file, and then referenced inline with the formula: [1]Sheet1!$A$1
. In this way, external resource files can more easily be determined and updated if needed.
Note that whenever a workbook contains a formula referencing another workbook, some values from that external workbook are also cached with the referencing workbook. This is done so that if a recalculation of the workbook is needed and the workbook isn't accessible, a cached value may be used to complete the calculation.