Leading the way in Microsoft Office Development

What is the Edit Links dialog box ?

If you want a worksheet to always display the same data as another you can link the data.

You can easily create links between your workbooks and between your worksheets

This way if you change the data in the original worksheet, the data in the linked worksheet will also change.

Links are created using formulas and have this format: "=[Book4]Sheet1!$A$2"

If you want to update the links after a workbook has been opened you can use the (Edit > Links) dialog box.

If the workbook does not contain any external links, then the (Edit > Links) command will be disabled.

This will only be enabled if your workbook contains links to external sources or other workbooks

When you open a workbook that contains one or more links to other workbooks you will be prompted and asked if you want to update those links.

You will only be prompted if one or more of the source workbooks is closed, if they are all open, then the links will be updated automatically.

Using the (Edit > Links) dialog box

Update Values - Updates the currently selected source in the Source list. The formulas containing any external references are updated.

Change Source - Changing a source allows you to change the location of the workbook on the network drive or to exchange the location of one workbook for another.

Open Source -

Break Link - Replaces the linked formulas with values. It is always sensible to make a backup of a workbook before breaking any links.

Check Status - This allows you to check the status of the link, options include "Unknown", "Source is open".

Startup Prompt -

Let users choose to display the alert or not -

Don't display the alert and don't update automatic links -

Don't display the alert and update links -

Locking the Links

Workbooks can contain links to other OLE compatible applications, such as Reuters and Bloomberg.

You can prevent updating these links accidentally by locking the links.

Select (Tools > Options)(Calculation tab) Remove the check mark in the Update Remote References box.

Renaming your Workbooks

If a workbook has link to another workbook and that workbook is renamed then the links will be broken.

The same is true if the workbook is moved to a different directory.

It is possible to redirect these broken links to the renamed workbook.

Select the source file in the list and click "Change Source". Browse to the correct workbook and the cell addresses will become valid.

Broken Links

It is possible to intentionally break the links to another workbook.

This can be done by selecting the source file and clicking the "Break Link" button.

When links are broken the formulas are replaced with values.

The links between workbook are broken, and the linked formulas are deleted.

Things to Remember

A quick way to update all the links in a workbook is to press F9. This method is only useful when you have your calculation set to manual (Tools > Options)(Calculation tab).

Links can only be updated from the target workbook.

The quickest way to link your data is to copy it from the source workbook, Activate the new workbook and select (Edit > Paste Special, Paste Link).