External References

As well as having references in cells to other cells inside the document, you can also have references to cells in other documents.Such references to cells in other documents are called external references.

This can be useful if you have spreadsheets which use figures from other spreadsheets. For example, you may need to have a turnover spreadsheet whose totals you use in your balance sheet spreadsheet. By using external references, you can link the two spreadsheets so that a change in the the total figure in the turnover spreadsheet is automatically applied to the balance sheet. So, by updating one spreadsheet, you are automatically updating another spreadsheet.

The document which contains the external references is called the dependent document. The documents referred to by the dependent document are called supporting documents. Normally, both dependent and supporting documents are stored in the same directory on your disc.

To include a cell reference from another document, you should have loaded both the dependent document you are editing and the supporting documents. To insert an external reference in the dependent document, click in the relevant cell in the supporting document whilst you are editing a number i.e. after having given the Edit Formula command. The external reference will appear on the formula line of the dependent document at the caret position as follows:

[filename]cell reference

where filename is the name of the supporting document and cell reference is the cell referred to in that document.

For example, [turnover]A3 would be a reference to cell A3 in the document called 'turnover'.

Instead of clicking in the relevant cell in the supporting document, you can type in the external reference on the formula line using the above syntax. Any document referred to should be loaded. If you try to type in a reference to a document which is not loaded, you will get an error message File not loaded. If you then load the supporting document, PipeDream will resolve the reference.

Once documents are linked in this way, loading a dependent document causes its supporting documents to be loaded at the same time. All the linked documents must be in memory at the same time for the linking to work correctly. If you do not have enough memory for all the linked documents to be in memory at the same time, a message will tell you.

Note that only supporting documents are loaded automatically. Dependent documents are not loaded automatically. If you want to ensure that all documents in a linked set are loaded together, make sure that they are all supporting documents.

Example 1:

Sheet A has references to cells in B, C and D. It is a dependent document.

Sheets B, C, D are supporting documents.

In the case shown above, loading the dependent document A causes the supporting documents B, C and D to be loaded automatically.

However, loading B, C or D does not load any other document automatically.

Example 2:

Sheet A has references to cells in B, C and D.

Sheets B, C, D have references to cells in A.

All sheets are both supporting and dependent documents.

In the case shown above, loading document A causes documents B, C and D to be loaded automatically. Also, loading either B, C or D causes all the other documents in the linked set to be loaded automatically, since references have turned them all into supporting documents.

References in all linked documents will be recalculated automatically, provided you have not switched to manual recalculation.

If you change the name of one of the supporting documents, all references to it in dependent documents which are loaded will change accordingly.

If you carry out moves, copies or deletions in one of the linked documents such that cell references need to be updated, this will be done automatically for all the dependent documents which are loaded.

Note that, if you have dependent documents which are not in memory when you do some editing, references in the dependent documents may become out of date.

If you try to close one of the supporting documents, you will be asked to confirm that you want to close it. Remember that all the linked documents must be open at the same time for the features described to work correctly.

If you load two supporting documents with the same name, you will get the message More than 1 file loaded in a spreadsheet.

External References and Directories

You can have references between files in different directories. PipeDream avoids, wherever possible, using a whole filename to describe the location of a supporting document; if the supporting document is in the same directory as a dependent document, only the leafname of the supporting document is required. If the supporting document is in a sub-directory of the directory containing the dependent document, then the sub-directory is used, with no pathname. If the supporting document is in a higher directory or in a different branch of the directory tree, PipeDream has to use the whole pathname. This works well until you give the file to someone else who does not have these directories or you take out the removable media on which a supporting document was stored. To avoid such problems, make sure that your supporting documents are in the same directory as, or in sub-directories of, the dependent document.

Loading of Files From a Library

If PipeDream is unable to load a supporting document from the directory of the dependent document, it searches the directories listed on the Path for a file with the given name in the sub-directory Library. If the file still has not been loaded, it searches the directories listed on the Path for a file with the given name but without any sub-directory prefix.

For example, if there is no file called custom in the current directory, the reference [custom] causes PipeDream to look for the name custom on the Path.

By saving library files in a library directory accessible on the Path, such as !PipeDream.Resources.Library, you can have one central copy of common data and custom functions.

In order for PipeDream to carry out library searching in this way, the external reference must not include any pathname.

For information on See
External references in custom functions Custom Functions
External references in names Names