Updating and managing external cell references

<< Click to Display Table of Contents >>

Updating and managing external cell references

Use the ribbon command Data | group Edit | External references external_ref_icon 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 external_ref_icon 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 doc_prop_icon 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.