- [Instructor] Alright, if you're using Google Sheets,
sometimes you want to bring in values from other worksheets.
So in this case, we want to bring in values
into this summary tab, this says all locations,
and we want bring them from some of these source worksheets.
So, we're going to go over how to do that right now
and some of the different nuances that you run into
as you work through it.
(soft upbeat music)
So, these are basically
just more complicated cell references.
So, your basic cell reference,
we'll just put a number in I6 here.
We wanted to bring that into F6.
You just hit the equal sign in this little gray bracket
underneath here, it's saying it's waiting for data.
So, it doesn't have a whole formula yet.
And you can just left click on I6, hit Enter,
there it is.
You bring the value from I6 into F6.
Now, in order to get that value from another worksheet,
we're just going to add a little bit extra.
So, we'll delete these,
and the value that I want, New York, January 2019,
it's actually in the New York Q1 worksheet.
But we'll talk about this a little bit later.
It's summarized into New York 2019.
So, I actually want to grab it from NY 2019.
So, we call these worksheets or sheets or tabs.
So, all three of those terms will be the same thing.
Worksheets is an older term from Excel.
Tabs is just kind of an informal term that you might use
because you're used to talking about browser tabs.
And if in the actual documentation they're usually referred
to as sheets.
So, I'll try to call it a sheet.
So, we'll hit the equal sign.
And now we want something from the NY 2019 sheet.
First, we'll do it the easy way.
And this is the way I would usually do it.
Is after I hit equals, I go to this sheet
and here's the value that I want.
It's in F5 over here.
If I left click on it,
it's already showing you what's going to end up
in that other sheet, okay?
So, this rectangle here is a preview
of what you're building and Google Sheets
brings it over and lays it on top of this sheet
so that you can see what you're doing,
especially if you're doing a longer formula.
And what it did was it uses the name of the worksheet,
NY space 2019,
and it surrounds it by single quotes.
Now those single quotes are optional if there is no space,
but in this case, there is a space.
So, they have to be there.
So, if you're not sure,
just always put them in.
And then the separator between the worksheet name
and the cell reference is an explanation point.
So, you have to type that in and then the cell reference,
which you don't have to type it in
if you just use a mouse like I did.
So let's hit Enter,
and we brought that value into the cell.
That's a 17, we'll go back and look, this was a 17.
Alright, so in practical applications,
you probably don't want just one, in this case,
we want these three and then we'll pull these,
let's get rid of this pivot table editor,
and then the ones from over here.
So these first three,
we can behave like we would normally
with simple cell references.
So, let's go down the 17.
We can just drag this down.
So, if I go into the lower right-hand corner
of this rectangle, you see that there's a blue square.
If you hover over that,
you get a plus as with simple cell references,
you can just left-click, drag it down,
I'm going to go one too far
and it's smart enough to increment it.
Okay, but it will just go straight down.
So, this 68 is not what you want.
That's a total, right?
You wanna jump to F13.
So, we'll go back here and we'll try to build this
from scratch with the keyboard.
I'm gonna do a single quote.
And it is guessing some of this New York 2019,
but we want to do F15 instead of F8.
So we'll say, "Hey, thanks for what you've given me here.
We'll take it."
But then we'll come back in and type the F15.
Alright, so we can drag those three down
Did I not get the right one?
We wanted F13,
drag that down three.
Alright, and we're moving along.
Alright, now that you've brought these values
into the cell, you can sum them.
Let's do that.
But this is kind of the long way to do this.
So I got 285.
Let's check to make sure that's right.
I'm going to hold down my Control key
and highlight 68, 70, 73 and 74.
Look in the bottom right hand corner,
it's 285.
So, we know sum formula is working,
but you don't have to bring all of these values
into this worksheet in order to then just do a sum on them.
So, you could just build a longer formula.
And before I build this formula,
what I'm going to do is I'm going to take this window,
I'm going to put it on the left-hand side,
and then I'm going to open up a new tab in my browser,
put it over on the right.
I lost it.
Where'd it go?
Here it is.
Put it over to the right.
I will take this website address.
I'll paste it into this browser.
So, I have the same worksheet up twice.
And because I want to see these cell ranges here.
Alright, so instead of pulling all the numbers in
to put them into a formula, maybe that's what you want to do
and that's fine,
but if you don't want to pull them all in,
you can just build a formula and use these cell references
inside of it.
So, I want to sum the first value is New York, 2019.
We'll use the separator, the explanation point,
and then we want F5 to F7, right?
And then separate the values by a comma.
So now we want to do New York 2019.
15.
Alright and you get the same value.
So for the next column, this was New York.
Now we're going to do LA.
We'll take a look at that sheet
and you'll see it's the exact same layout
with the same cell references,
but just a different worksheet name.
If you add a lot of these columns,
let's say you had a little bit more foresight
and when you wrote these formulas,
you fixed the column reference.
So, when we copy this to the right,
we want that to stay as an F.
We'll do that for all of these.
So, what that dollar sign is going to accomplish
is when you copy this to the right,
the F won't change and the 5 won't change anyway,
'cause we're not going to drag it down.
So, let's highlight all of these.
We're going to drag them over to the right.
And they're the exact same formula now
because we fixed the column reference.
So, we will highlight all of them.
And the only thing we really wanna change
is the name of the worksheet, right?
So, we're going to go to edit, find and replace.
We want to find New York space.
Well, really, we just wanna find New York,
let's try that.
New York, change it to LA, replace all,
and it didn't do anything but we need to collect
that we also want to search within formulas.
So, we'll click that.
And it did something interested
and changed it from values to formulas,
just so you could see what you're doing.
Let's say replace all,
there it goes to LA, we'll click Done,
switches it back in the values.
Now we're pulling in LA.
So, we'll prove that out.
We'll go to LA, I'll hold down my Control key,
scroll to the right so I can see everything,
59, 79, 76, 72, that total is 286.
And let's sum this and see what we get.
There we go.
That's working.
Now, there are some faster ways to pull in this data.
So, I had used this file for another video quite a while ago
and what I had done here was create pivot tables
from a different worksheet.
So, if I left click into one of these,
we'll just maximize this for now.
Zoom in.
Each one of these pivot tables is made
from a different worksheet,
but it's done in the exact same way.
So, when I click into one of them and I look at the source,
which is this top row, and you hover over Select data range,
the data range just has a worksheet named before it, right?
And single quotes, explanation point, and then the cells.
So, you'll see this throughout as you work with worksheets
more that other worksheets are referenced
and sometimes it's built in
and you don't even know how it's being done.
Alright, we're going to step down a level here
and work with these tabs a little bit.
Call them tabs,
we call them sheets, worksheets, whatever.
New York Q1.
This has a lot of data, right?
So, the New York 2019 was more of a summary tab.
This is more of the actual transactions.
So, there's 36 rows here,
and let's go back to all locations and say,
we wanna pull in-
let's just say, we want to pull in all of that data.
Alright, so there's a few different ways to do it.
Let's just go down to some empty space.
So, we'll say a cool function called sort.
So this returns in array,
meaning that it will write out all the data to below it
and to the right of it, not just in this one cell.
Sort, and then the range is going to be-
here we go.
Single quote.
Q1, exclamation point for the separator.
And then we'll say A1 to C36, alright?
And so once you know what column to sort it on,
we'll do, we'll say Column 1,
that's where the dates were, is ascending or not.
We'll just say true.
Make it in ascending order and hit Enter.
It looks like the headers disappeared
but if you go down to the bottom,
it's sorted those headers because it put them on the bottom.
What you could do is just make the range
start in A2 and then copy and paste the headers
and that way, it won't sort them
like it's a part of the table.
Alright, so that's a fast way to bring the data in.
And if you did that and you came down to the bottom here
and repeated the Sort function,
you could put all the worksheets together in a long row.
So, this process can be a little bit manual.
It's not too bad. (upbeat music)
But if you're looking for a more automatic way to do it,
you check out this next video that I've linked to.
But most of all, thanks for watching.
It's nice to have you along.
I'll see you in that next video.