Creating Links Between Worksheets and Workbooks

Linking is the process of dynamically updating data in a worksheet from data in another source worksheet or workbook. When data is linked, the linked data reflects any changes you make to the original data.

Linking is accomplished through special formulas that contain references known as external references . An external reference can refer to a cell in a different worksheet in the same workbook or to a cell in any other worksheet in any other workbook.

Excel lets you links data from other worksheets and workbooks in these ways:

• Reference another worksheet in a linking formula using sheet references

• Reference several worksheets in a linking formula using 3D references

• Reference another workbook in a linking formula

When you build a linking formula, you type the formula in the cell where you want the results to appear.

Referencing Another Worksheet in a Formula

If you have a lot of data and you create many worksheets to store this data, you might have occasions when a formula in one worksheet needs to use data from another sheet. These formulas are called sheet references . They're useful because you don't have to create redundant data in numerous sheets.

To refer to another cell in another sheet, place an exclamation mark between the sheet name and cell name . The syntax for this type of formula looks like =SHEET!Cell . Use the correct sheet name in place of SHEET if you have named the sheet.

The next exercise helps you build a linking formula to reference a cell on another sheet.

To Do: Create a Link Between Worksheets in a Formula

1. Press Ctrl+F6 to move to the Sales 1st Qtr workbook. In the Detail sheet, in cells B5:B9, enter the following numbers : 100 , 125 , 150 , 215 , 185 . Then, widen column A to accommodate the long entry in cell A6.

2. In the Summary sheet, click cell J16. Type Soft Cover and press Enter.

3. Cell J17 will contain the linking formula. Type = .

4. Click the Detail sheet tab.

5. Click cell B6.

6. Press Enter. Excel calculates the formula.

   

   If you know which cells you want to reference, you can type the entire linking formula in a cell. If you're not sure of the cell address that you want to reference on another sheet, you can start writing your formula and switch to the sheet that you want to reference when you get to that part of the formula. Then click the cell or range of cells that you want in your formula. The cell or range reference appears automatically in your formula. You can then finish your formula and press Enter to calculate.

   
   

7. Click cell J17. Now you should see the linking formula =Detail!B6 , which links the data in cell B6 on the Detail sheet to cell J17 on the Summary sheet. The formula bar contains the formula, and the correct answer is 125, as shown in Figure 14.8.

   Figure 14.8. A linking formula that references another sheet.

   

When a sheet name contains spaces, such as Sales 2004, you need to place single quotation marks around it when you are making sheet references, as in 'Sales 2004'.

Linking Several Worksheets

What if you have a formula that needs to reference a cell range that has two or more sheets in a workbook? This might happen if you have identical worksheets for different budgets , sales teams , or regions . You also might have several different worksheets that have totals calculated and entered in identical cell addresses. You can then add all these totals to get a grand total by referencing all the sheets and cell addresses in one formula.

When you have cell ranges such as this, Excel refers to them as 3D references . A 3D reference is set up by including a sheet range, which names the beginning and end sheets, and a cell range, which names the cell to which you are referring. A formula that uses a 3D reference that includes Sheet1 through Sheet5 and the cells A4:A8 might look something like this: =SUM(SHEET1:SHEET5!A4:A8) .

Another way to include 3D references in your formulas is to click the worksheets that you want to include in your formula. To do this, start your formula in the cell where you want the results. When you come to the point where you need to use the 3D reference, click the first worksheet tab that you want to include in your reference, hold down Shift, click the last worksheet that you want to include, and select the cells you want to reference. When you finish writing your formula, press Enter.

The next exercise helps you build a linking formula to reference a cell on another sheet. Before you create the formula, you'll need to copy the Detail sheet so that you have two sheets of data to calculate.

To Do: Create a Link Between Multiple Worksheets in a Formula

1. Click the Detail sheet. Point to the Detail Sheet tab, hold down the Ctrl key, and drag to the Org Chart tab. Excel duplicates the Detail sheet, creating a new sheet with the name Detail (2).

2. Click the Detail (2) sheet tab and then click cell A4. Then type New York and press Enter.

3. Click the Detail sheet tab and then click cell C15. Then type Boston and New York and press Enter.

4. Cell C16 will contain the linking formula. Type =SUM('Detail:Detail (2)'!B5:B9) . Be sure to type a space between Detail and (2) .

5. Press Enter.

   

   Another way to include 3D references in your formulas is to use the mouse and click the worksheets that you want to include in your formula. To do so, start typing your formula in the cell where you want the answer to appear. When you get to the point where you need to use the 3D reference, click the first worksheet tab that you want to include in your reference, press and hold the Shift key if the worksheets are consecutive, click the last worksheet that you want to include, and select the cells you want to reference. If the worksheets are nonconsecutive, press and hold the Ctrl key, click the worksheets you want to include, and select the cells you want to reference. When you finish entering your formula, press Enter.

   
   

6. Double-click cell C16. In this cell, you should see the linking formula =SUM('Detail:Detail (2)'!B5:B9) , which links the data in the range of cells B5 through B9 on the Detail sheet through the Detail (2) sheet. The Formula bar contains the formula, and the correct result is 1550 , as shown in Figure 14.9.

   Figure 14.9. A linking formula that references several sheets.

   

7. Press Enter to deselect cell C16.

Linking Workbooks

When you are linking workbooks, the workbooks have some special names that you need to know about. The workbook that contains a linking formula is the dependent workbook, and the workbook that contains the linked data is the source workbook.

If you're referencing a cell in another workbook, the syntax is [Book]Sheet!Cell . When you enter a linking formula to reference a cell in another workbook, include the workbook name enclosed in brackets, the sheet name followed by an exclamation mark ( ! ), and the cell reference.

In the next To Do exercise, you use the Window command to switch between two open workbooks, Sales 1st Qtr and Sales 1st Qtr 2. Perform the steps that follow to build a linking formula that links the workbooks.

To Do: Create a Link Between Workbooks in a Formula

1. In the Sales 1st Qtr workbook, click the Summary Sheet tab. This workbook will contain the linking formula.

2. Click cell L16. Type Sales 1st Qtr & Sales 1st Qtr 2 and press Enter.

3. Cell L17 will contain the linking formula. Type = , click cell B9, and type + .

4. Click Window in the menu bar and select the Sales 1st Qtr 2 workbook.

5. In the Summary sheet, click cell B9. This cell contains the data you want to link to the first workbook.

6. Press Enter.

7. Double-click cell L17. In cell L17, you should see the linking formula =B9+'[Sales 1st Qtr 2.xls]Summary'!$B$9 , which links the data in cell B9 on the Summary sheet of the Sales 1st Qtr 2 workbook. The Formula bar contains the formula, and the correct result is $3,486.82 , as shown in Figure 14.10.

   Figure 14.10. A linking formula that references another workbook.

   

8. Press Enter to deselect cell L17.

Updating Links

When you are working with multiple workbooks and linking formulas, you need to know how the links are updated. If you change data in cells that are referenced in linking formulas, will the formula results be updated automatically? Yes, as long as both workbooks are open.

If the data in the source workbook is changed while the dependent workbookthe one that contains a linking formulais closed, the linked data is not immediately updated. The next time you open the dependent workbook, Excel asks whether you want to update the data. To update all the linked data in the workbook, choose Yes. If you have links that are manually updated or you want to update the links yourself, choose No.

To Do: Update Links Manually

1. Press Ctrl+F6 to switch to the Sales 1st Qtr 2 workbook. Then close the workbook and remember to save the changes.

2. In the Sales 1st Qtr Workbook, click cell B8.

3. Type 510 and press Enter.

4. Select Edit, Links. The Edit Links dialog box opens, as shown in Figure 14.11. The Source File list box contains a list of all linking resources used in the active workbook. The link reference you want to update, in this case, Sales 1st Qtr 2, is already selected. The status of the source file is Unknown.

   Figure 14.11. The Edit Links dialog box.

   

5. Click Update Values. The status of the source file changes from Unknown to OK.

   

   The Update Values button in the Links dialog box is available only if the source workbook is not already open and if any linked formulas have been changed since the last update. In the Edit Links dialog box, you can click the Change Source button to change the link reference. Or you can open the source worksheet by clicking the Open Source button in the Edit Links dialog box.

   
   

6. Click Close. The linked formula in cell L17 is updated from $3,486 to the new value of $3,496 .