Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
get larger and more complex, you may need to reference data that's on another worksheet, or in another workbook, on another file within your hard drive somewhere your local drives, or something that exists on a shared server. Let's look at how to refer to external data within a formula. I've created a simple chart for you that's ready for printing. It's placed in the Extras folder in your student files. You'll notice that this version of Quarterly_Sales has three tabs at the bottom, and that those tabs have been named. We have Annual Sales, Bonuses and Projected. The Projected tab is really a mirror image of Annual Sales. It's meant to represent a target or a sales quota. Your sales quota is something that you're trying to get to; your actual sales are the results of your efforts. So if you look at the Bonuses tab, this is a sample for an individual salesperson Mark Swift, oh hey, that's me, who's attempting here to track their progress. Now this particular salesperson is working for the West division, so the row of information that I'm interested in is here at B3, C3, D3 and E3. If you look at the formula that's been created for the projected totals, you'll see that we're multiplying the commission rate at 15%, by a value that exists in a different worksheet. The worksheet that's named Projected has been placed here in front of the cell reference. So you have C11, your commission rate, times Projected B3, and the name Projected, which is the name of that worksheet, has been followed by an exclamation mark to let Excel know that I want to refer to external data. This is the simplest type of external reference you can get. The name for that worksheet does not contain any special characters or spaces, and we have just a simple cell reference. C11 times B3, only B3 exists on a different page, so I've added an explicit direction for this formula to find that value on the other worksheet. If we go to that worksheet, you'll see that B3, the sales quota for first quarter was 35,000. In the following three quarters, it's 30,000. So we can copy this formula over to second quarter. Ah, but we get nothing. How come? Let's take a look at the formula and see where it went wrong. Well, let's see we have T11 times Projected C3. I know I did want to move to the next cell over. Here we're looking at B3 and here we want refer to C3, so we move over to the second quarter. Ah, there's the problem. The problem lies in this cell reference.
Excel automatically incremented all of my references that I made here to move them one over. It's a very intuitive move, but in this case it worked against us. This goes back to a topic that we discussed just a little while ago about absolute and relative cell references. Because this is a relative cell reference to C11, when I copy the formula to the right, it incremented it to D11, which is empty. How do we correct that? Well first of all we need to eliminate this formula, so let's get rid of that, go back here, and instead of correcting both of them, let's correct the source by adding strings here, and here. Those two strings make the column and row constant. So we're always going to be referring to the value that's within C11. Hit Enter, and now if I copy that formula to the right by clicking and dragging we have a formula that says the value that's in C11, which always represents our commission rate, times the value that's on the sheet Projected in C3, which is our second quarter sales quota, and of course now I can click and drag that across the rest of the cells and we have our sales quota projected for all four quarters. Let's do the same thing here for Actual. Now before we make any changes to this formula, to correct for the absolute cell reference, let's take a look at the differences between B4 and B3. Well B4 is referring to a worksheet that has a very simple name. In B3 we're referring to the first sheet of Annual Sales, and because there's a space in the name of the worksheet, needed to add ' around just the name. That allows Excel to skip over spaces and work with special characters.
So if you have a name that's longer, you need to add those single quotes so that it processes it properly. If you remove those quotes you'll get an error message, #VALUE, which is telling you that it doesn't understand where the value's coming from. There's an error somewhere and it's pointing. So let's go ahead and correct the cell reference. String C11. Here we go. Hit Enter, and now this formula works. It's referring back to our Actual Sales totals for B3; 43,600, excellent. And I'll copy that over all three cells, and there we go. We have an easy comparison for all four quarters from our sales quotas to the actual sales for the year. Those are two fairly simple examples of external references. They're both referring to worksheets within the same workbook, but if you look at the chart that's provided in your Extras folder, you'll see there's not a lot more to it if you're referring to a file that exists out in the hard drive or on your server, you'll need your single quotes around your path name, because invariably your path will contain special characters or spaces, and then you simply refer to the cell within that table that you want to pull your value from.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.