how to reference formulas and data on other worksheets

You can put the answer to a formula on a different worksheet. It doesn't have to go into the same sheet you're working on. For example, think back to the exam marks spreadsheet we did earlier. We had a spreadsheet that had the average scores for the students. Below that we had each exam mark as a letter of the alphabet: A, B, C, etc. The spreadsheet we created was this one below:

Instead of putting all those Grades on the same Sheet, we could have put them on Sheet2 of the Workbook.

If you want to put a formula on a different work sheet, you have to set a reference to the Sheet that contains the numbers going into the formula. An example might clear things up.

• 
  
  Start a new spreadsheet


• 
  
  In cell A1 of Sheet1 enter the number 4


• 
  
  In cell A2 of Sheet1 enter the number 5


• 
  
  Click on Sheet2 at the bottom of the spreadsheet


• 
  
  Click inside cell A1 of Sheet2


• 
  
  Click inside the formula bar and enter this formula:

=Sheet1!A1 + A2

• 
  
  Press the Return key on your keyboard


• 
  
  Excel should give you the answer 4

Four plus five is clearly not four, so what went wrong? Well examine the way we set a reference to the sheet that held our numbers. It was this:

=Sheet1!A1

When you are setting a reference to a different worksheet, you need the name of the Sheet. Then you type an exclamation mark (or a bang as it's sometimes known). You then type the cell that you are referring to. So in our formula, we were saying to Excel "Find the worksheet that has the name Sheet1. Now find Cell A1 on the workbook called Sheet1."

However, the whole formula was this:

=Sheet1!A1 + A2

For the second part of the formula, we have + A2. But we haven't told Excel the name of the Sheet we're referring to. We've just put A2. If the cell reference in the formula doesn't have the name of a worksheet in front of it, Excel will assume you mean the current worksheet. Our current worksheet is Sheet2. Cell A2 of Sheet2 is empty. So the formula adds up the number 4 from cell A1 on Sheet1 and the blank cell A2 on Sheet2. Which gets an answer of 4.

To solve the problem, tell Excel that you want to take the number from cell A2 on Sheet1. So change your formula to this:

=Sheet1!A1 + Sheet1!A2

This time, Excel will give you the correct answer of 9.

The main point to bear in mind when referencing data that is on another worksheet is this: Give Excel the name of the worksheet followed by a Bang!