Join Dennis Taylor for an in-depth discussion in this video Using formulas to link worksheets and workbooks, part of Excel 2016 Essential Training.
- In this movie, we need to have two workbooks open. One, "09-04-RegionalSales", we're currently seeing. And, by pressing control-tab, the other one we need to see is "09-04-EmployeeTable". Let's look at the regional file first. We can also switch files by going to the switch windows button on the view tab, in the ribbon. Over to regional sales. Its not uncommon to write formulas that get data from other worksheets within the same workbook. And, in this workbook we've got sheets for East, Midwest, South, Pacific, and that's not a requirement for what we're about to do, at least initially.
We've got another sheet called, Regional Totals. We simply want to write a formula here that gets data from the other sheets. I'm going to start with equal. Click on the East sheet. I'm aiming for the retail total, here. When I go to the East sheet, that retail total is in cell F3, so I'll click it, then a plus. And now I'm going to go to the Midwest sheet. These sheets do have the same locations, so I'll be clicking F3 in each one of them. As I'm doing this, you might note in the formula bar, how the formula's being built.
I am not typing sheet names or cell addresses at any time, as I do this. Another plus. I'll go to South and click the appropriate cell, plus. And then, go to Pacific, click the appropriate cell. No final plus needed. Enter, we've got a result. Here's the formula, I double click. I didn't type sheet names, notice how sheet names are followed by exclamation points, that's what Excel does. In these examples, all of the sheet names are followed by exclamation points.
If the sheet name has a space in it, and none of them do, you'll see single quotes in there, as well. So, even though I don't spell Midwest with a space, I'm going to do it momentarily. I'm going to change the sheet name of Midwest. I'll double click that sheet tab and put a space between D and W and press enter. Back to the formula, what's happened? Double click, we can see this, single quotes. Doesn't make the formula any worse, really. Still workable, readable, we get correct results. I have, however, seen certain formulas that gather data from multiple sheets, if the sheets have spaces in them, it just makes certain formulas like a little bit longer, a little bit more complex, maybe.
But certainly nothing wrong with it. If I jump back here to Midwest, double click again. I'll take out that space, the formula will have adjusted. Back here, all set. Now, since in each of these sheets, the wholesale and internet totals are just below retail, we don't have to write formulas for the others. I'll simply drag from the corner. By the way, this is not to suggest that when you're writing formulas that get data from other sheets, that you're always getting them out of the same cell. We could be gathering multiple data from different locations on these different sheets.
This kind of a formula, however, poses some problems if we have more sheets. What if we have eight regions? Ten regions? Twelve regions? Or what if we're working off of a model that has 50 states in it? This is going to get unwieldy. So, there's another approach to this. We'll use it in the current model even though we have only four sheets. Let's just start by putting in sum. And you can do it fast with "alt, equal" if you wish. Or you could have typed. Now, what I'm about to do is to click the East sheet and with the shift key, click Pacific.
So, at one end or the other, we're going to click the sheet. That's East. And then, with the shift key held down, I'll click Pacific. And, in the formula bar, you can see, the formula's being built and its almost ready but it needs a cell reference. And that cell reference is going to be F3. I'll click it and enter. That's the same total we saw before. If I expose all these formulas, we can do it on the formulas tab by choosing show formulas. You see the difference, we got to make column B wider there.
Here we are. Quite a bit shorter. And, substantially shorter if we were dealing with, if can imagine, 50 states. If this were a 50 state model, we might see something like "SUM(Alabama:Wyoming)" and that's it. But you can imagine what this might look like if we were going to each of the tabs with the state name appearing there. So, this is a very efficient way to write certain kinds of formulas. Provided, the data is in the same locations. Once again, back to this button, show formulas. Now, we have another workbook open.
Writing formulas that gather data from different worksheets, in different workbooks, sometimes pose some problems. But we can write these as well. You always have to keep track of those and that's done in the next movie. We'll talk about how we edit links. Well let's go to the other file. On the view tab in the ribbon, we can choose switch windows, and employ table. Over here, we want a total for couches. And in that other workbook, we have to go to each of the other sheets. I'm going to zoom in here, using the slider bar in the lower right hand corner, click plus a few times.
That's one way to use it. Like this, so we can see it more clearly. I'm going to start a formula here. Equal, but I need to get to the other workbook. We can use the view tab or control tab. View tab in the ribbon, switch windows, let's go over to regional sales. And go to East first. And, here's the grand total we need for couches. Right there. Put in a plus. Then go to Midwest. Click here, put in a plus. Similar to what we did before but remember, this formula's actually starting in another workbook.
Go to South, click here, put in a plus. And then, Pacific. And click here, and enter. There we are. Now, its pretty long because we're dealing with data out of a different workbook. So, I scroll to the right, that might loop around a little bit. But here's the idea, and remember, I typed a very little here. I went to the other workbook and the data we're seeing right here is from the East sheet, cell B7. And then we see this being repeated. And by the way, formulas, here's a little trick that you can use.
In really, really long formulas, its a bit difficult to read. You can introduce a line break by way of alt, enter. As I'm doing right now. And that makes a little bit easier to read. You can decide where you want to do the break here, I'll do this right before the plus. And we see it looking something like this. Now, this isn't changing the result, but I think it might clarify what we're doing. And remember, I typed almost nothing here. I typed equal, I went to the view tab, switch windows to get to the other workbook.
And then, one by one, I clicked on the various sheets and the particular cells. And then I pressed enter. Now, recliners is to the right of this in these other examples, and we could copy that too. But, writing formulas, the deal with date and different worksheets are problems because first of all, I would close this file and save it. I would close the other file and save it, too. And they're probably in the same folder, but are they necessarily? No, they're not. And what if we move files to different folders? First of all, what I want to show you is what happens when we close the other file? I'm going to go to the other file.
Now, keep in mind before I do that, what this formula looks like, is this. The other file is open right now. I want to jump to the other file, either with control tab or by way of switch windows. I'll use control tab this time. I want to close this file and save it. So, I can press control W, that's a fast way. Or go through the file menu. Control W, do I want to save this? Yes. Okay. Now, look at this. Not only do we need to see the reference to the other workbook, but we need to see the reference to where this file is.
I'm going to zoom back a little bit. And, its quite long. In other words, each of these examples here, we've got a cell reference, a sheet reference, and a file reference. And that file is in a folder called Chaper 09, which is in a folder called Exercise Files, Desktop, with my name, users, C drive, so on. It looks awfully long and unwieldy. Now, based on what we did, this isn't that complex, really. But it sure looks kind of overwhelming when we're seeing it like this. That's true in the sense that what we're seeing here is accurate.
It shows us that link. Now, what if I go out and move that other file? We've got a problem here. And that's the issue when you're writing formulas that deal with data from different workbooks, that's what you always have to be aware of. And any time you find yourself in that environment, its good to make notes and remember that you do have linkage setup. And in the next movie, we'll talk about how to manage some of those links.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros