Create linkage formulas that refer to other worksheets and workbooks

- [Instructor] When you're trying to gather summary information, sometimes it's necessary to write a formula that gets data from different worksheets in the same workbook. And although less common, sometimes you need to write formulas that gather data from different open workbooks, different files. In this particular workbook, there are four sheets, California, Texas, Florida and New York, that have exactly the same layout, and there's also a summary sheet here. And we want to write a formula on the summary sheet that gathers data from the other four sheets. And we can do this in a couple of different ways.

If we've got 10 sheets here, 50 sheets, 51 sheets here, the formula we're about to see is not going to work so well. So we're going to show you two different methods here of writing formulas that gather different data from these four sheets. So, I'm going to start in cell B4. Now, in every one of the other sheets, which have exactly the same layout, the Retail Chair's total is going to come out of cell B4. Now, when you're writing formulas that work with other worksheets, do not type the worksheet. It's just an opportunity for a typing mistake, and you probably wouldn't know it at first that the sheet names are followed by an exclamation point.

So I'm going to type = and click the California sheet, and click on cell B4, and put in the +. And in the formula bar, you see the formula. I didn't type California, remember, I clicked it. I didn't type B4, I clicked on it. But we need to do this three more times. So, I'll now click the Texas sheet, B4 and +, and then Florida, B4 and +, and then New York, and click on cell B4, no final +, and enter.

We've got a total, and that's what it looks like. And how would that look if we had 10 states? And then if we had 51, including all of the states and DC? An unwieldy formula, to be sure. Now, sometimes you'll see spaces in sheet names. There's nothing wrong with that, although it can cause some problems. I'm going to go to the New York sheet here, and double-click the sheet tab and put a space between New and York. And now our summary formula back here looks a little different. New York is embedded in single quotes.

Nothing truly wrong with that. You have up to 31 characters per sheet name, and sometimes spaces are appropriate, but in certain formulas, and here's a small example, it makes the formula slightly more complex maybe, something like that. If you have lots of these, it makes the formula busier, more crowded than it needs to be. Nothing truly wrong here. We'll there for a bit and show you another example later where that's going to be a problem. So this is one way to do this, and not the best. I'm going to copy this to the right, and leave it there, and actually leave it just on the right side, because we will make a comparison with a better way to do this.

Suppose instead of that long formula, we begin with this process. I'll simply press Alt + =, this simply saves me the time of typing SUM(). Now I'm going to select those first four sheets. I'm going to click the California sheet, with Shift key held down, I'll click the New York sheet. The formula has put the data, California:NewYork in the formula. What's lacking from the formula is the cell reference. I'll click cell B4, and enter. Same total we saw before, much much shorter.

And off to the right, here, I'll double-click here and just put a space in front of it, leave that there, and here's the formula over here. And I'll make Column F a little wider, too, so we can see that formula, and then double-click. A lot shorter. Remember that formula to the right, the one that uses the four actual sheet names, will grow and grow and grow, and that's going to become unwieldy at some point. The formula to the left worked beautifully. If we had all 50 states plus DC, this probably would be if it were alphabetized, Alabama:Wyoming.

Much, much better. And let me do it one more time, since it is a little bit tricky. Begin with AutoSum, type it or press Alt + =. Then select the four sheets. Click California, with the Shift key, click New York, click cell B4, and enter. There it is. So, copy downward, drag across, and of all these, readjust the column-widths, and we're all set. That's one kind of a linkage formula that's really going to be efficient and very effective in lists where you've got the same layout.

This could be a series of months, it could be a series of regions, or states as we see here, countries, whatever, a lot of different examples where that would come in handy. Now, here's a different situation on a different worksheet over here. I've got a formula in place that's picking up total sales from California. I'm also thinking ahead to when I might have 10 states in the model, eventually 51, so I'd like to be able to copy this down the column. I think you know this is not going to work. We'll type in here, that picks up data from G7 on the California sheet, this picks up data from G8.

Why can't we pick up the Texas sheet? Well, we actually can. I'm going to move this off to the right, and double-click, put a space in front of it, just as a reminder to what that formula's doing. And what I'd like to do now is to pick up the California sheet, and then Texas, Florida, New York, using a function called Indirect. We're going to construct the formula here that in effect takes this California name out of cell A2, and combines it with, this is where we use the concatenation symbol, &, meaning And, and we're trying to construct something that looks like that previous formula.

So what comes after the actual sheet name, California, within double-quotes we now want to see what !G7, and that's within double-quotes. And I'll press Control + Enter here, so the actual cell doesn't move, we got a total, let's drag this down the column, those appear to be correct answers. Oops, what's happening with New York, here? Earlier we put a space in the actual sheet name, you see it down there, so that's not working.

So what could we do here? We might have to readjust this formula, but that would make it inconsistent with the others. So what can we do here? Is putting a space out here going to help? No, that didn't help either. So what should we do? Let's undo that, and let's go back and change the sheet name. Back to New York, double-click, get rid of the space. Back to our totals here, and now that's working. So that's an example of why we sometimes would need to not have spaces there, and if we were going to be adding the other states, all those two-name states like North Carolina, South Dakota, and so on, we would want to have these entries without a space between the two names.

So this is an example here of using a function called Indirect to allow us to gather data from different sheets. Now, another example over here could work better if we've got two worksheets showing at the same time. Here's a sheet called MasterList, it's got some social security numbers. We've got another list somewhere else, and we're trying to do a match here. Now, we can write a formula without seeing the different sources of data at the same time. But, by going to the View tab, we can introduce a new window.

Now, at first that seems to do almost nothing, but then back to the View tab, and Arrange All, and choose Windows of Active Workbook. Now, if you haven't created a new window, clicking here OK doesn't seem to do much. But I did create a new window. So what happens now? We've got two windows on the screen. They might not be the ones we want. I want the sheet on the left to be my master list. So I have to select that sheet for the left side of the screen.

I want that, and I can zoom in an element, independently of the one on the right, and now as I write a formula, I do emphasize this is not a requirement, but it might make it easier to write certain formulas, if we can see the different locations involved. I want to use a function called Match to simply let me know if this particular Social Security number right here is found on the other list. So, what do I do next? I click in the other worksheet, and then I click Column C, comma, and I need to get an exact match.

If you're unfamiliar with the Match function, we put in 0 here, means exact match. And Enter, and if I do have an exact match, it will tell me where within Column C, that the exact match occurs. It's in the eighth position, and since I did choose the entire column here, that corresponds with Row Eight. And so we see in the other workshop over here, right there in cell C8, there's that number. Same number we're seeing over here. That was not truly necessary, but it enhances formula writing in some cases, because we can see the receiving and sending areas at the same time.

Notice that the title bar says 103-LinkageFormulas1, the other one says 103-LinkageFormulas2. The name of the file is 103-LinkageFormulas. So now we've got two windows of the same file. I'll close one of them, and expand the other one. Sometimes you need to write a formula that gathers data from a different workbook. I'm going to create a new workbook. Control + N means New. There's a new workbook. It'll be called Book1, Book2, something like that. Let's imagine that I want to write a formula here that gathers data from another workbook.

Now here too, I have the option of seeing the other workbook or not. I think in these cases, you want to see the other workbook. Let's go to the View tab here, choose Arrange All, this time not Windows of Active Workbook, so uncheck that. Tiled is usually the preferred option, and you can experiment with this. Click OK, we're seeing two workbooks. This is Book1, the brand new one on the left, there's the other one on the right, and I want to be pulling in, let's say just this information right here by way of formulas, because some of this will change.

So, over in cell A2, I'm going to write a formula, =, I'll click on the other side of the screen there, click there first, and then cell A13. Notice that that's an absolute address. If I'm going to be copying this, I don't that to be absolute, so highlight the address portion, press F4 a few times to make sure that's not an absolute address, and Enter. I'll copy that two cells to the right to pick up the corresponding information. That might not be in the correct number notation, but we'll worry about that. This needs to be copied down about six more rows here.

And readjusting the column widths, double-click, I've got my formulas. But notice here, a formula like this, for example, from right to left the cell location, the name of the sheet, but also the name of the file. Let me see what's happening out there. Now, I haven't saved that other file, but if I were to close that other file after naming it, I'd also see the file name here. Now, as a general rule, and certainly not a hard, fast rule, I like to steer people away from writing formulas that get data from other workbooks.

And yet, I do recognize that it sometimes is necessary. And I think most often when you are working with formulas that were already there that deal with the different workbooks, do open the other workbook. It just makes working with them a lot simpler. So, we've seen lots of different examples here of writing linkage formulas. I think for many people, the most common use of these is to write formulas where we deal with data within the same workbook, but we've seen this latter example how it's sometimes necessary to write formulas that gather data from different workbooks that are open.

Closing the other workbook and saving it is okay, we can still do this, but it makes it all harder. These are powerful tools which we sometimes need to use as we work with Excel.