Join Dennis Taylor for an in-depth discussion in this video Using formulas to calculate values across worksheets and workbooks, part of Excel Tips Weekly.
- For many users it's not uncommon to write formulas in some worksheets that get data from other worksheets within the same workbook. Less common but also needed at times are formulas in one workbook that need to get data from worksheets located in another workbook. Let's cover some of these features. In this workbook Linkage Formulas, we've got data for four different states. Now this data happens to be alike in terms of layout. That's not a requirement for some of the things we're going to be doing, but for other things it will be.
I've got a sheet called Totals here, and what I'd like to do is get the total for California here. Now jumping over to California we'll see the total here is in cell G8. And the totals for the other three states are the same. Part of the reason we might be doing this too is we're imagining this model might be growing. Maybe we're a major retailer, but we only have stores in these four states right now. We're gonna be expanding, and eventually we'll see more states here and then we'll have more sheet tabs. We simply wanna get a total here.
There are two approaches to writing a linkage formula. One is to begin at the source data, so let's go to California. Here's the data in question, we could copy this, right click and Copy, do it that way, or press Control C. Then go to the destination, that's the Totals sheet right here, and go to Paste Special. You can do this with a right click Paste Special or with the keystroke shortcut Control Alt V. Paste Special dialogue box, Paste Link, and there's a formula.
Notice that the formula is an absolute address, many times that's not important, sometimes it could be. But do note that. A better way, certainly a faster way to do this is to start at the destination, right here. I'll just delete this. Start right here with the = sign. Then click the other sheet name, California. Click the cell in question, G7, press Enter. Same answer, this time the address is not absolute, and again for the moment, that makes no difference.
Now what would be beneficial is if we could copy this downward. I think you know this is not going to work. If I do this right now, I will get data from California from cell G8 and G9 if I do this, and G10 and so on. That's not what we want. Now the way to do this uses a function that's really unusual. It is hard to describe without a definition. It's called INDIRECT. Before doing this, what I'm gonna do is take a new function in Excel and use it here. This is often used for documentation, it's called FORMULATEXT.
It's new in Excel 2013. When you see it appear, you can simply click it and tab it into place. FORMULATEXT displays the actual formula. And all I'm going to do for the moment is take two cells and move them off to the right strictly for reference purposes. I'll be using the function called INDIRECT, a function that's difficult to describe without an example. What we're trying to do is to get the data from A2, A2, and then we wanna make this look like what we're seeing in cell E2, so we wanna combine California along with, and we use the & symbol, meaning and, so what's going to be following California? Within " ! G7 " and Enter.
and there's the answer. Same answer as we saw over here. That's the function. Now if we copy this downward, we should be picking up Texas, Florida, and New York in the appropriate entry. And yet something's not going right here. What's happening? New York has a space in it. Now there's nothing wrong with a space in a sheet name, we see that down below, the yellow tab. It's gonna make life a lot simpler in this particular example by getting rid of the space right here. That still doesn't clean things up, but changing the name of the sheet. Now again, at different times you might want to consider eliminating spaces from sheet names, they're not really wrong, and yet at times they pose little problems like the one we're seeing here.
If we get rid of the space in both situations here, and then go back to our Totals, we do have a correct answer. Any sheet name that has a space in it when it's referred to in a formula, sometimes you'll need additional information. If we were to do this over again manually, and what we could do here would take a little bit of time but it would be something like this. Let me go back and put in that space again for New York. And then match it up right here. Now what we need to do is to use INDIRECT and start with " ' " that's the ' that was before New York.
And then we wanna get A5, in other words New York, the text as we see it there. And after that, " and then a ' ! G7 as we saw it before. So that's gonna take a little bit of work, so I think you saw the advantage of, let's get rid of the space in New York. In both cases it works a lot easier than this. So different examples here of coming up with linkage formulas and in this case the objective was to be able to copy them as well. Now there's another kind of calculation that we might wanna do, and this involves perhaps multiple sheets.
As we go back and look at California, and Texas and Florida, and I said at the beginning, these all have the exact same layouts. Here's a sheet called Summary that also has the exact same layout. And it's all set up to be gathering totals from the other sheets. We can do this in two ways. One quite long and compared to the other, quite short. So the longer method. I won't be doing much typing actually, but a lot of clicking. I'm on this Summary sheet, I wanna get data from California, Texas, Florida, and New York. I'm in cell B4, and on all the sheets, that's where we see Retail Chairs numbers.
= , so I'll click California, click that number, put in a + Then Texas, click that cell, put in a + . Notice in the formula bar, the formula's being built. It's getting lengthy, and Florida, click that cell and put in a + and then New York, click that cell, no final + , complete the entry, 5,000. Somewhat lengthy. And what would happen if we had five states, six states, seven states, 20 states? All states? Quite lengthy. At some point unwieldy.
We'd surely make a mistake in clicking back and forth here. Notice how this does work though. Now a better way is simply to start with auto SUM. You can do that with Alt = And now what we're gonna do is gather the four states. And we do this by way of a technique of clicking the California, or the first sheet, then holding down the Shift key and clicking New York. And in the formula bar, you see the formula being built. All it lacks is the cell address. So we'll simply click cell B4.
All done. Enter. Some answer as before, much shorter. And what if we start adding other states? As long as we put those states between California and New York, we make no adjustment to this. If we start putting names at the end of New York or after New York, we're gonna have to rewrite the formula a few times. So this means all states between California and New York as well as California and New York, of course, picks up all the data. So in this model here, we would simply drag across and down or down and across, makes no difference which direction we go first, and we have a worksheet like this.
This kind of formula, and also its predecessor, the longer one, both of those are sometimes referred to as 3D Formulas. And so these kinds of formulas only work when we've got data that has the same layout. Sometimes you need to write formulas that get data from other workbooks. Suppose on this Totals sheet I've got some other data I'm interested in. I have another workbook open. I'm going to press Control Tab to get to it. If you have multiple workbooks open, you can press Control Tab, if you have more than one file open, Control Tab will take you to the next one, the next one, the next one, and so on and back to the current one.
So the more files you have open, the less efficient Control Tab is. Nevertheless, it's handy here. What I'd like to do is to possibly get some totals for some of these entries, maybe for the moment I'm only thinking of Chairs. So back here in my other report in this workbook here, put in Chairs. Now I'm about to write a linkage formula like before. Remember before I typed = I clicked on the appropriate worksheet, but now in this case, I have to get to the other workbook. I could certainly press Control Tab, that's one way.
Another way is to go to the View tab in the Ribbon, Switch Windows button, and click on the name of the other workbook. That'll take us over to that workbook. Click the appropriate sheet, it's in the Sales Report sheet, and I'm going to click the Total here for whatever item I want. Chairs, Enter. There it is. Now double clicking here to expose the formula. Formulas that deal with data from other workbooks need to include not only the cell reference and the sheet reference, but also the name of the file.
And here too we'll see single quotes in here because the sheet name has a space in it. Anytime you're typing formulas like these, in the previous ones where we saw different worksheets too, don't find yourself typing the sheet names. It's nearly always more efficient and certainly going to be more accurate by clicking on the various sheets and the various sheets and file names that you find. So here's a formula that's getting data from another workbook. And what happens if we close that other workbook? This number will still be here.
I'll jump to that other workbook by way of Control Tab. Here it is here. And I'll simply press Control W to close it. I didn't make any changes to it. And now let's look at this formula. This is particular formula is now based on cells located within a certain worksheet within a certain workbook called Product Totals. And that workbook is within a folder called Exercise Folder. And so on and so on and so on folders within folders on the F Drive. Now of course you wouldn't be typing in a formula like this, and it's highly unlikely to be creating formulas based on workbooks that are not open.
And this would be almost impossible to do, in other words you'd have to be typing this out exactly to get it right. When you're setting up formulas with data on different workbooks, be sure to have the file open. And once again, if I go back and open the file, and I'll simply press Control O, open the file, here's that same file. We're back here, now it's open. Let's now take a look, I'll jump over to the other workbook. Now the formula looks a lot shorter because the other file is open. Many times I suggest for people that ask questions about this particular feature, be a little bit cautious about this.
I almost wanna steer people away from writing formulas that deal with data on different workbooks. And yet I do recognize it's necessary at times. But there is that possibility at times that you save a file in a different location, you move it from one folder to another folder, you're just not aware of all the links that have been set up, and so you run into problems there. But we've seen techniques here for writing linkage formulas dealing with data across different worksheets and the same workbook, and different worksheets from different workbooks.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Appropriate for all
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.