<< Click to Display Table of Contents >> Updating and managing external cell references |
Use the ribbon command Data | group Edit | External references to update and manage the external references in a document.
Choosing this command opens a dialog box that lists all files the current document refers to.
The buttons in this dialog box allow you to perform the following actions:
▪Update
Updates only those external cell references that refer to the file currently selected in the list. (See also "Notes on updating external cell references" below) |
▪Update all
Updates all external references in the entire document. |
Tip: Alternatively, you can trigger Update all via the ribbon command Formula | group Update | Update data | Update external references.
▪Open
Opens the external file currently selected in the list. |
▪Change
Lets you choose a different file for the external file currently selected in the list. All references to this file in your document will be adopted accordingly. |
▪Previous
Jumps to the previous cell that contains a reference to the file selected in the list. |
▪Next
Jumps to the next cell that contains a reference to the file selected in the list. |
Click on the Close button to exit the dialog box.
Notes on updating external cell references
Important: The values returned by external cell references are not updated automatically every time the worksheet is recalculated.
External cell references are updated only in the following cases:
▪When you open a document, all external cell references it contains are updated automatically (once).
You can disable this automatic update – see the options described below. |
▪After that, you can update external references manually whenever needed.
To do so, choose the ribbon command Data | group Edit | External references and use the Update or Update all button in the dialog box as described above. |
Alternatively: Use the ribbon command Formula | group Update | Update data | Update external references which does the same as the Update all button. |
Options for updating external cell references
There are some options related to updating external references in the document properties.
To change these, choose the ribbon command File | group File management | Properties and switch to the Calculate tab.
There, the following options are available in the External references section:
▪Update data on opening
Lets you choose if all external cell references in a document should be updated (once) when the document is opened: |
Always: When the document is opened, update its external references. |
Never: When the document is opened, do not update its external references. |
Ask user: When the document is opened, ask the user if its external references should be updated. |
▪Save external data in document
If this option is enabled, PlanMaker saves a copy of all values returned by external cell references in the document. If the file for an external reference is missing when the external reference is updated, the value last stored will still be displayed. |
If you disable this option, PlanMaker instead displays an error value when you update an external reference while the corresponding file cannot be accessed. |
Note: The above options aren't global settings but document properties. This means that you can set them up individually for each document.