XX Linking Worksheets

Difficulty Level: Advanced
Excel Version: XP
Assumed Knowledge: Beginner

 

 

The aim of the tutorial is to:

 

explain how worksheets can be linked such that extracts from different worksheets can be incorporated onto a single worksheet.

 

 

Linking Formulae across Worksheets

The above example shows formulae using cell values that appear on the same worksheet. As each book in Excel can contain more than worksheet it is possible to use the values from different worksheets within the same workbook in a formula. The only difference to the cell address is that it should follow the name of the worksheet and an ! and follow the format "sheetname!celladdress". For example, the value of cell A1 in worksheet 1 and the value of cell B4 in worksheet 2 can be added and the formula for this would be "=Sheet1!A1+Sheet2!B4". If you have renamed any worksheet then include the names in the formula. Imagine that a book about Expenses contains the first worksheet called Travel, the second called Accommodation and the third called Total Expenses. If we want to add Travel cell B10 and Accommodation cell C14 and put the result in Total Expenses cell E5 then then the formula entered into Total Expenses E5 would be =Travel!B10+Accommodation!C14:

This formula can either be typed in full or the mouse can be used to click on the worksheet tab and relevant cell to build up the formula.

To add, subtract, multiple or divide two cell values in different worksheets:

  1. Click on the cell where the result is to appear.
  2. To begin the calculation type =
  3. Click on the worksheet tab.
  4. Type in an !
  5. Click on the cell.
  6. Type in + to add; - to subtract;* (SHIFT+8) to multiply; / to divide
  7. Click on the worksheet tab.
  8. Type in an !
  9. Click on the cell.
  10. Press ENTER.
    The formula will appear in the formula bar.
    The result will appear in the designated cell.

To find the sum of a range of two or more cell values in a different worksheet using AutoSum:

  1. Click on the worksheet and cell where the result is to appear.
  2. Click on the AutoSum button on the standard toolbar.
  3. Click on the worksheet tab with the cell range to be summed.
  4. Select the cell range to be summed.
  5. Press the ENTER key on the keyboard.
    The result will appear in the designated cell.

In the illustration below cell A4 has been chosen to show the sum of the values in the cells ranging from A1 to A3. When the AutoSum button has been activated the cell address window at the top left will contain the function name SUM, the formula =SUM(A1:A3) in the formula bar with a : indicating a range of cells, a blue dotted border around the range of cells containing values to be summed, and the formula appears in the cell where the result will appear:

In this example the formula is correct. When the ENTER button on the keyboard is pressed the result will appear in the selected cell: