excel tutorialExternal References
An external reference (also called link) is a reference to a cell or range of cells in another Excel workbook. This chapter teaches you how to create an external reference and how to update external references.
Open the Workbooks
To create an external reference, execute the following steps.
1. Open the source workbooks (Sales.xls, Production.xls and Logistics.xls). These three workbooks (departments) contain the data we want to refer to.
2. Open the Summary workbook. This is the workbook of the manager that contains the external reference.
We are now going to calculate the total cost and total profit.
Create External Reference
When creating external references, it's good practice to refer to defined names in other workbooks.
1. Don't know how to name a cell? Go here: Names in formulas. Below you can find the result for the Sales workbook. Do the same for the Production and the Logistics workbook.
Ok. Time to create the external reference.
2. Open the Summary workbook.
3. Type the equal sign, =
4. On the View tab, click Switch Windows and then click Sales.
5. In the Sales workbook, select cell B2.
6. Type the plus sign, +
7. Repeat step 4, 5 and 6 for the Production and the Logistics workbook.
Result:
In a similar way you can calculate the total profit.
Note: Change the costs in one of the source workbooks. The Summary workbook is updated automatically.
Update External Reference
Close all workbooks. Change the costs in one or more source workbooks and close all source workbooks again. Now open the Summary workbook. Excel will ask you whether or not you want to update the links. A Security Warning will appear.
1. Click Enable Content to update the links.
To manually update only some of the links to other workbooks, execute the following steps.
2. Click the X (your data might be out of date at this point).
3. On the Data tab, in the Connections group, click Edit links.
The Edit Links dialog box appears.
4. Select a workbook and click on Update Values to update the link to this workbook.
Note: see how the status changes to OK.
Startup Prompt
Execute the following steps to turn off the Startup Prompt (Security Warning).
1. Click on Startup Prompt (see previous screen).
2. Choose 'Don't display the alert and update links' to turn off the prompt and update the links automatically (or choose "don't update automatic links" if desired).
This only turns off the Startup Prompt for the Summary workbook. There is an Excel option to turn off the Startup Prompt for all possible workbooks. Not recommended!
3. Select the File tab, click Excel Options and choose the Advanced category.
Do not uncheck this option, or you will not be aware of updates.
Did you like this free Excel tutorial? Show your appreciation, vote for us.