External References

 

Create External Reference | Alert | Edit Links

An external reference in Excel is a reference to a cell or range of cells in another workbook. Below you can find the workbooks of three divisions (North, Mid and South).

North

Mid

South

Create External Reference

To create an external reference, execute the following steps.

1. Open all workbooks.

2. In the Company workbook, select cell B2 and type the equal sign =

External References Example

3. On the View tab, in the Window group, click Switch Windows.

Switch Windows

4. Click North.

Click North

5. In the North workbook, select cell B2.

Select Cell

6. Type a +

7. Repeat steps 3 to 6 for the Mid workbook.

8. Repeat steps 3 to 5 for the South workbook.

9. Remove the $ symbols in the formula of cell B2.

Result:

External References in Excel

10. Copy the formula to the other cells.

Alert

Close all workbooks. Change a number in the workbook of a division. Close all workbooks again. Open the Company workbook.

A. To update all links, click Enable Content.

B. To not update the links, click the X.

Enable Content

Note: if you see another alert, click Update or Don’t Update.

Edit Links

On the Data tab, in the Queries & Connections group, click Edit Links to launch the Edit Links dialog box.

Click Edit Links

1. If you didn’t update the links, you can still update the links here. Select a workbook and click Update Values to update the links to this workbook. Note how the Status changes to OK.

Update Values

2. If you don’t want to display the alert and update the links automatically, Click Startup Prompt, select the third option, and click OK.

Don't Display Alert and Update Links

Previous articleCapital Investment in Excel
Next articleActiveX Controls in Excel VBA