- [Instructor] 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 gonna 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 we'll have more sheets 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 total sheet right here and go to paste special.
You can do this with a right click pay special
or the keystroke shortcut Control + Alt + V.
Paste special dialog 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 equal 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.
And it's 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 formula text.
It's new in Excel 2013.
When you see it appear, you can simply click it
and tap it into place.
Formula text displays the actual formula.
And all I'm gonna do for the moment
is take these 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 ampersand symbol meaning "and".
So what's going to be following California
within double quotes, exclamation point G7,
double quote, 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.
And we see that down below at 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 wanna 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,
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 double quote, single quote, double quote,
that's the single quote 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, double quote, and then a single quote,
exclamation point 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 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, equal.
So I'll click California, click that number, put in a plus,
then Texas, click that cell, put in a plus.
Notice in the formula bar, the formula is being built.
It's getting lengthy.
Then Florida click that cell, and put in a plus,
and then New York, click that cell, no final plus,
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 lengthily at some point, unwieldy.
You'd surely make a mistake and clicking back
and forth here.
And this is how this does work though.
Now a better way is simply to start with auto sum.
You can do that with Alt + Equal.
And now all we're going to 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.
Same answers before, much shorter.
And what if we start adding other States?
As long as you 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 in the cross,
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 total 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 opened,
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,
I put in Chairs.
Now I'm about to write a linkage formula like before.
Remember, before I typed equal,
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 formula is 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 particular formula is now based on cells located
within a certain work sheet 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.
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 different techniques here
for writing linkage formulas,
dealing with data across different worksheets
in the same workbook
and different worksheets from different workbooks.