Skip to main content

Linking to other Excel 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!

In the next part, you'll learn how to Insert Drawing objects into your Spreadsheets.
Labels:

Comments

Post a Comment

Popular posts from this blog

Beginners PHP  -This is a complete and free PHP programming course for beginners. It's assumed that you already have some HTML skills. But you don't need to be a guru, by any means. If you need a refresher on HTML, then click the link for the Web Design course on the left of this page. Everything you need to get started with this PHP course is set out in section one below. Good luck! Home Page > PHP Section One - An Introduction to PHP 1. What is PHP and Why do I need it? 2. What you need to get started 3. Installing and testing Wampserver 4. Troubleshooting > PHP Two - Getting Started With Variables 1. What is a Variable? 2. Putting text into variables 3. Variables - some practice 4. More variable practice 5. Joining direct text and variable data 6. Adding up in PHP 7. Subtraction 8. Multiplication 9. Division 10. Floating point numbers > PHP Three - Conditional Logic 1. If Statements 2. Using If Statements 3....
Post a Comment
Read more
Visual Basic .NET Contents Page   -This computer course is an introduction to Visual Basic.NET programming for beginners. This course assumes that you have no programming experience whatsoever. It's a lot easier than you think, and can be a very rewarding hobby! You don't need to buy any software for this course! You can use the new FREE Visual Basic Express Edition from Microsoft. To see which version you need, click below: Getting the free Visual Studio Express - Which version do I need? > VB .NET One - Getting Started   1. Getting started with VB.NET 2. Visual Basic .NET Forms 3. Adding Controls using the Toolbox Home Page 4. Adding a Textbox to the Form 5. Visual Basic .NET and Properties 6. The Text Property 7. Adding a splash of colour 8. Saving your work 9. Create a New Project >   VB .NET Two - Write your first .NET code   1. What is a Variable? 2. Add a coding button to the Form 3. Writing y...
Post a Comment
Read more
The Excel SumIF Function  - Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument: SUMIF( range ,  criteria ,  sum_range ) Range and criteria are the same as with  CountIF  - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.) Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed. So in Column A, enter the names: In Column B enter how much each person owes: In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't: Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something li...
Post a Comment
Read more