Cells Worksheets and Workbooks in Expressions

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Cells, Worksheets, and Workbooks in Expressions

Introduction

So far in our expressions, we were selecting cells to combine their values to the operators and all the cells we used belonged to the same worksheet. In a typical workbook, you use various worksheet. Sometimes you will want want to a value stored in a cell of a separate worksheet and want to involve the value of that cell in the expression. Of course you can copy the value and paste it where needed. The problem is, when/if that value changes, you would have to change it in your expression. Instead of memorizing the value, you can create an expression that would directly use the value in its cell.

Using Various Worksheets in an Expression

In Microsoft Excel, yon can create an expression that involves cells of two or more different worksheets. To proceed, in the cell where the expression will appear, type the assignment operator "=". If you know the name of the worksheet where the other cell is located, type that name. If the name of the worksheet is in more than one word, include it in single-quotes. If the name is in one word, you can omit the single-quotes. After the name of the worksheet, type the ! operator. If you want to use one cell, type its name. If you want to use a range of cells, type that range. If you want to use different non-adjacent cells, for each cell you want to use, type the name of the worksheet followed by ! and the name of the cell.

Instead of remembering the names of the worksheets and their cells, and as we have done so far with cells, you can start an expression with the = operator. Then, when you need a cell located in another worksheet, click the tab of that worksheet and select the cell. Microsoft Excel would take care of the names behind the scenes. When you end the expression, you will be taken back to the worksheet where you were working.

Practical Learning: Using Various Worksheets in an Expression

  1. Open the CPAR - Accounting Records.xlsx workbook and click the Income Statement tab
  2. Click Cell J7 and type =
  3. Click the Transaction Analysis tab
  4. Click Cell M11 and type +
  5. Still in the Transaction Analysis tab, click Cell M12 and press Enter
  6. Back in the Income Statement sheet, click Cell H9 and type =-
  7. Click the Transaction Analysis tab
  8. Click Cell M17 and press Enter
  9. Back in the Income Statement sheet, click Cell H10 and type =-
  10. Click the Transaction Analysis tab
  11. Click Cell M18 and press Enter
  12. Back in the Income Statement sheet, click Cell H11 and type =-
  13. Click the Transaction Analysis tab
  14. Click Cell M19 and press Enter
  15. Back in the Income Statement sheet, click Cell H12 and type =-
  16. Click the Transaction Analysis tab
  17. Click Cell M10 and press Enter
     
  18. Observe the values in Cells H10, J13, and J14 of the Income Statement sheet.
    Click the Transaction Analysis tab
  19. In Cell C18, type -2,890 and press Enter
  20. In Cell M18, type -420 and press Enter
  21. Click the Income Statement tab and notice that the value in Cell H10 has been updated, so have the values of Cells J13 and J14
  22. Save the workbook

Using Various Workbooks in an Expression

You may have one (or more) value(s) stored in the cell(s) of a (different) worksheet(s) and the worksheet(s) belong(s) to a different (or various) workbook(s). To use such a values, you could copy it and paste it in the cell that holds the expression you are creating. As mentioned for the worksheet, if that value changes, you would have to manually update the expression. Fortunately, you can create an expression that involves various workbooks and link them so that, when the value of an involved cell changes, the result of the expression would be automatically updated.

To involve the cell of a different workbook in an expression, start with the = operator. Type the name of the workbook as a file and, because it is a file, include its extension. Because the name of the workbook includes a period, you must start that name with the left square bracket "[" and you must end with the right square bracket "]". Just after the right square bracket, type the name of the worksheet where the cell is located. This name, whether the name of the worksheet is in one or more words, don't include it in single-quotes. Instead, the combination of the name of the workbook and the name of the worksheet must be included in single-quotes. After the the combination of the name of the workbook and the name of the worksheet, type the ! operator. As reviewed for the worksheet, the ! is followed by the name of the cell.

As done for the cells of one worksheet or a cell in a different worksheet, after starting the expression, you can access the other workbook and click the desired cell. If you use this technique (as opposed to typing the names), Microsoft Excel would start the name of the cell with $, followed by the letter-name of the cell, followed by $, and followed by the number of the row.

Practical Learning: Using Various Workbooks in an Expression

  1. On the Ribbon, click View
  2. In the Window section, click View Side by Side.
    In the Window section of the Ribbon, click Switch Windows. If CPAR Accounting Records.xlsx does not have a check mark, then click it
  3. In the CPAR Accounting Records.xlsx workbook, click the Transaction Analysis tab
  4. On October 13, 2008, the company fixed a car for a customer. We don't remember how much was paid. We will get the value from the Repair Orders worksheet and put it in the Transaction Analysis worksheet. We will also update the income satement.
    In the Transaction Analysis worksheet, click Cell C20 and type =
  5. In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
  6. In the worksheet, scroll down and click Cell J35
     
    Linked Worksheets
  7. Press Enter
  8. In the Transaction Analysis worksheet, click Cell M20 and type =
  9. In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
  10. In the worksheet, scroll down and click Cell J35 and press Enter
  11. Access the CPAR Accounting Records.xlsx workbook and click the Income Statement worksheet
  12. Click Cell J7
  13. In the Formula Bar, click the expression and press End
  14. Type +
  15. In the CPAR Accounting Records.xlsx workbook, click the Transaction Analysis tab
  16. Click Cell M20 and press Enter
     
  17. In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
  18. In the worksheet, click Cell H17, type 45.35 and press Enter
  19. Notice that the total of the invoice has changed.
    Access the CPAR Accounting Records.xlsx workbook and check that the values have been updated
     
  20. Save both workbooks