- [Instructor] All right on this channel,
we've done a lot of videos on protecting ranges
of cells or entire worksheets.
So in order to understand what we're going to go through
in this video, we'll do a quick history lesson here.
And what we've done in the past is let's say, for example,
we don't want anyone changing the wages, right?
So we'll highlight column E, go to data,
and just do Protect sheets and ranges.
So you can do this, you get a dialogue box up here.
I'll put a link to a previous video that walks
you through the steps and the different ways to set it up.
But what this does is this protects
who can edit it, not who can see it.
So I have a lot of users asking on these videos
in the past, basically, how can we provide
another level of protection?
And they want to have users in this spreadsheet,
but they want to completely hide certain data from them.
And you may have tried to come down here,
left-click on this, and do Hide sheet.
Well, don't do that if you're trying
to actually hide data from people
because they can just go look in the list of all the sheets,
see that it's still there,
and click on it, and it comes back.
If they have view only access to the spreadsheet
they won't be able to see the hidden sheet,
but if they make a copy of the file
they'll be able to see it then.
So that's more of an organizational consideration.
If you have a lot of sheets here
you can just hide them from view.
But if you really want to control who sees certain data
we're going to walk through a scenario here
to show you how to separate it.
And then, once you have it separated,
how to bring it into that new sheet,
and summarize it in a meaningful way.
So you have the Detail tab, this is
what you don't want people to see.
And we also have a Summary tab here.
So we've just created a pivot table
to summarize the payroll by quarter.
You're okay with everyone seeing this,
just not with them seeing the detail.
Let's go up and see who has access to this right now.
(indistinct) is just me and Michael.
But we don't want Michael to be able to see the actual wages
and associate them with specific people.
So we're going to come on to here, come down, Remove access.
You have to remember to click Save.
And let's go back up here to make sure of one more thing,
and that it's the link sharing is off.
So, in this case, I would've been in trouble.
Let me make that collapse again.
Right now, the link is shared within my organization
so Michael would still be able
to see this, if he had the link.
Let's come here, click on this,
and make it so it's restricted.
Meaning that only the people added
at the top here will have access.
So let's click on Done.
Now, I'm the only person that can see this spreadsheet,
but I want Michael to be able
to see summary information, right?
So I have the Summary tab.
What we're going to do is pull this data
into a completely different file, right?
So I have one started here, I called it Payroll Summary.
It's just a blank spreadsheet,
nothing happening in this spreadsheet right now.
And if I click on the Share button,
which you should do to make sure it's only me,
and I must have these being defaulted to be shared
within the organization, I'm going to turn that off
in this case for a best practice since it's payroll data.
Clicked on, and both of these right now are only me.
So in order to allow Michael to see this summary data
without having access to the detail, let's go into this.
I'm going to call this a destination sheet
because this is a one way link.
We're going to use a formula called IMPORTRANGE.
I've linked to a page with a little
more description on IMPORTRANGE.
That's on a sister site called Sheets Help.
And that's a site that I'm working on
that has resources for how to use Google Sheets.
So I'll leave that link for you.
And the first thing this function wants
is this spreadsheet URL.
And that's the spreadsheet of the source file, okay?
So let's come over back to the source file,
we'll click on that.
This is the entire URL, you can grab all of this,
but you don't need all of it, you really just need
this long string of random characters right in the middle
in between the slashes where we'll get that
to make the formula a little shorter.
So I will do Control + C to copy that.
We'll come back to the IMPORTRANGE function
and don't forget your quotes since this is a string,
you need to start it with a quote.
And then, we're going to paste that spreadsheet key
into there, and end it with a quote.
Now, a comma and it wants the range string.
So this also needs quotes which is a bit unusual
in spreadsheet functions so don't forget that.
So I start it with quote, come through here,
and let's just grab B4 through C9.
So this is a pivot table, this will still live
on this worksheet, but we'll individually link
to each one of these cells, and bring this in.
So we'll do B4 to B9, let's do this.
In a second, we'll talk about the limitations of this,
and why we wanna do this a little bit differently.
But we'll close this off and hit Enter.
And the first time it does it, it gives you an error,
because it can't access that spreadsheet.
So when you hover over that,
you need to click on Allow access.
Just do it one time and it will pull everything in.
Oh, that's because I made this B4 to B9.
Let's make it B4 to C9.
Okay, we have this working now, let's go up,
and share this spreadsheet just with Michael.
See, we will click on him.
We're going to leave the other setting
as restricted, so it's just the two of us.
Now, Michael can come in here and see exactly
what we're seeing, but he still can't see the detail.
All right, but one consideration that you need
to keep in mind with import range is that you've given
this spreadsheet complete access to the payroll detail.
So right now, it's only accessing the Summary tab,
but if you were to have a user that got a little bit curious
they could try guessing and do something
like Sheet1! well maybe Sheet2,
and try to pull in some detail.
And, in that case, they guessed the worksheet wrong
because I called it Detail.
If they got lucky enough to guess that,
(typing)
they would start getting into the detail, right?
So you expand this C maybe to E19
and they start seeing some confidential data.
So my solution to this would be to come
in here and just make sure that you have
a somewhat unique name for the detail.
Just the word detail, would probably be hard enough
to recognize, but you could also just call it,
Detail-DontLookHere, and that will be enough.
I don't think anyone will ever guess that.
We'll come back in, now that breaks that
so they won't be able to see the detail.
We will go back to the simple cell reference,
and that'll bring up the summary data from the other file.
And if you'd like to learn more
about how to create pivot tables
we have a playlist up on the screen right now,
several different videos will walk you through the process.
Thanks for watching, it's nice to have you along.