We have previously seen how a formula containing a cell reference links two cells in a worksheet.

When the value in the referenced cell is changed, the result of the formula is automatically updated.

The same is true when a formula contains a reference to a cell in a different worksheet in the same workbook, since the whole workbook is a single file.

It is, however, possible to link two cells in two different files.

This provides you the flexibility of being able to use data from separate files and have the data update in both places as soon as the new file is saved.

External Reference Formula--a formula that contains a reference to a piece of data outside of the current workbook.

Dependent Document--the workbook that contains an external reference.

Supporting Document--the workbook that supports the dependent document.

A simple external reference is a reference to a single cell, range, or name in the supporting workbook.

A complex external reference contains formulas and/or functions using external references.

An external reference does not necessarily update automatically, even if the "automatic calculation" option is enabled.

The rules are as follows:

If the "Ask to Update Automatic Links" option [Tools>>Options>>Edit Tab] is off, external references will update automatically, even if the supporting workbooks are closed.

If the "Ask to Update Automatically Links" option [Tools>>Options>>Edit tab] is on, external references will NOT update automatically if the supporting workbooks are closed. Instead, when the dependent workbook is opened, a dialog box will open asking if the links should be re-established.

If "Yes" is selected, the external references will be updated with the supporting workbook still closed.

If "No" is selected, the cell with the external references will NOT be updated and will retain the value it had when it was last saved. In this case, the links can be updated at any time by using the Edit>>Links command.

Open the two sample worksheets [Prod.xls and Prodytd.xls] and save them to your network folder.

Select B3 in Prodytd.xls. This is an example of a simple external reference.

If you look at the formula bar, you will see that it refers to the value of a single cell $H$2 in the Prod.xls worksheet.

Select cell B7 in Prodytd.xls. This is an example of a complex external reference. Look at the formula bar, it refers to the SUM of cells $B$2 through $B$7 in the Prod.xls worksheet.

Activate the Prod.xls worksheet.

Change the value in B2 to 55

Note that the year-to-date total for Apples and the monthly totals for all fruit in Prodytd.xls changes as well.

The Paste Link command provides a simple way to create simple external linking formulas.

Simple links can also be entered as a formula as for complex links.

Using the same two worksheets, activate the Prod.xls worksheet and select cell H3.

Choose Edit>>Copy

Activate the Prodytd.xls worksheet and select cell C3.

Choose Edit>>Paste Link

Link the remaining values for Bananas, Grapes, Peaches and Kiwi’s using the Paste Link command.

Complex links require that a formula be entered and linked to data on the supporting worksheet.

Using the same worksheets, activate the Prodytd.xls worksheet and select cell C7.

Click the Autosum button on the Toolbar.

Activate the Prod.xls worksheet and select cell range C2:C7.

Press Enter.

Link the remaining values for Mar, Apr, May and June.

Open the file Finance.xls.

Complete the worksheet by linking data on the other worksheet found in the folder or sub directory.

Use an IF statement to computer a 10% profit sharing bonus if profits exceed $54,000.