Linking

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.

The workbook that contains the formula is called the "dependent" workbook and the workbooks that contains the data being referenced is the "source" workbook.
If the values in the source workbook change, the formula that references them can update its results automatically.

If the source workbook is open when you open the dependent workbook, the formulas are automatically updated without any prompt.
If the source workbook is closed when you open the dependent workbook you will be asked if you want to update the linked information
Update - will update all the formulas containing external references.
Don't Update - the formulas are not updated and keep their current values.

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

Status - There are a number of different statuses that you might see: OK, Source is Open, Unknown, Error.
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. This displays the Change Links dialog box,
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 - Displays the Startup Prompt dialog box. Allows you to customise what happens when the workbook opens.

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.

To prevent this from being displayed you should set the DisplayAlerts to False is the code.

Application.DisplayAlerts = False 

Caused by a Range().FillDown that contains links referenced to an external workbook.
Caused by an invalid VLOOKUP function being pasted into a cell that refers to a named range that doesn't exist.

Removing any Links

There are a number of things you can try in order to find and remove any unwanted links:
1) Using (Edit > Find), search for "[".
2) Changing (Edit > Links), refer the links to themselves.
3) Checking Chart Source Data, removing any #REFS.
4) Checking Chart Titles, Axis Titles and Data Labels, removing any #REFS.
5) Checking Named Ranges, removing any #REFS.

1) Using (Edit > Find)

Select (Edit > Find) and search for the open square bracket "[".
Every external link must have an open and close square bracket that refers to the workbook name (e.g. [Book1.xls]Sheet1!A1 )

2) Changing (Edit > Links) Source Data

Select (Edit > Links) and highlight the name of the workbook you want to remove.
Press the Change Source button and refer the link back to itself.
In the Browse dialog box, select the workbook that you are currently working in.
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.

3) Checking Chart Source Data

Check the series formula for any charts in the worksheet
Sometimes source data can get linked to other workbooks or may have become invalid.

4) Checking Chart Titles, Axis Titles and Data Labels

It is possible to link your titles and data labels directly to cells and therefore can get linked to other workbooks.

5) Checking Named Ranges

Sometimes named ranges can get linked to other workbooks or may have become invalid.
Check all the named ranges and remove any "#REF!"s.

If your workbook contains a lot of named ranges you might find it useful to print the list.
Select (Insert > Name > Paste) and press Paste List.
For more information about printing all your named ranges please refer to the Listing All Named Ranges page.

Re-Saving the Workbook

It is possible that a workbook might contain a link that does not appear in the (Edit > Links) dialog box.
Try saving the workbook in the same folder and with the same name as the broken link.
Any links that refer back to itself are automatically broken.

Check the (Data > Consolidate)

Select (Data > Consolidate).
If any items in the All References box point to any unwanted links then highlight them and delete.

Important

If you are unable to open a workbook it is possible to write a formula to try and extract information from it (e.g. "[badfile.xls]Sheet1!A1" ).
While you are trying to find any unwanted links you might as well clean up your workbook by removing any invalid named ranges.
You can introduce invalid links into a workbook if you rename a referenced workbook when the dependant workbook is not open.