How to work on another Workbook in Excel — External References

This chapter shows how to Create External Reference, Alert , Edit Links in Excel.

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).

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 =

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

4. Click North.

5. In the North workbook, select cell B2.

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:

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.

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

Edit Links

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

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.

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.