I Created an Advent Calendar

Beginner

About this Tutorial

Just in time for the holidays. Great for making promotion calendars to reveal promotions day to day.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

IMPORTRANGE()

Video Transcript

<div>0:01 Hey, so I made this Black Friday advent kind of calendar to reveal deals as they come along each day every single day for eight days I'm revealing a new a new deal for Black Friday But I wanted to make this better and I wanted to show you one really cool thing you can do with import range here to basically 0:19 have two sheets one of them public one of them private and you can have private information on the private sheet and then only show public information that you want to show on the public sheet I'll show you how so I have this calendar which I will down below better sheets members can get this for free 0:36 at the spreadsheet is included in this tutorial but I'm going to also make a copy which you won't be able to get so let's call this private advent calendar and what we're going to do is That's all. 0:48 All we need is the URL of the sheet so I'm going to put in different things so right now the two sheets are exactly the same they're designed exactly the same here you have dollar two three everything is the same right but on my private sheet I'm going to write two dollars here and I'm going to write 1:07 three dollars here because that's actually what the deal is going to be it's going to be two dollars for a product three dollars for a product but the four and five are going to be different but you'll see if you're following along my black Friday deals you'll see but just to show you that these two  1:21 sheets are now different so how would I actually reveal this deal well what I can do is do an equals import range and what I need is a URL and a range so I'm going to actually take the entire URL here of the private one and I'm going to put it in here I'm just going to put it in quotes and the range   1:39 string I'm going to have is exactly the same, ah, cal, ah, same sheet name and also the same cell number so it's D8. 1:50 I'm going to put that in quotes as well.

And it's loading and I need to allow access and now it's $2 and see now I can change the private one, if I change this to 2, or actually if I change it to, let's say 2, Thank you. 2:09 And now it's 2, right? So now the private one shows up correctly on the public one. But let's say I want to it's actually, let's reverse that to $2. 2:21 And now each day all I have to do is go to each one and update on the public one, the, ah, the cell here. 2:32 So it goes equals, import , right? Actually I need only one equals sign . And instead of d8 we're gonna write f8. 2:41 There we go.

And now the cool thing is, right? That, like, you think, oh someone has access to this, they can edit it, but I didn't give people access. 2:49 I have a share up here and it's just view access only. They don't have edit access . And if somebody copies this, because when we give away, like, this sheet, we're like showing this sheet, we're keeping data in here and people are checking it sort of each day. 3:03 People might copy it. And so they will copy it and they will have this URL, they will have this cell. 3:10 They'll have this import range . They'll have the URL of the private one.

But as you noticed, when I had to, when I put this import range in, I had to allow access . 3:18 So what happens is they copy the sheet, they have the exact same import range , and they have to allow access , but they don't have the private access. 3:27 They don't have the access to the private sheet. So that, that can't happen. They can't copy the sheet and get the import range from here. 3:34 This is all locked down, but isn't that really cool, right?

So we can have now two sheets, one which is public and one which is private, and then we can update the public one as we want, as we need to, right? 3:48 So we can write $3 in here. We can write, I can even go in, write this, this one here, and then it can update the text over there. 3:58 I thought that was really cool, and again, I'm gonna give you access if you're a BetterSheets member. Remember watching this on BetterSheets.co down below is a link just like on every single sheet. 4:08 On every single tutorial there's a link to this sheet. Go grab it.

You can get this design if you want to use it. 4:15 Also if you just want to check out import range . Again. Just update it with your URL. Try it for yourself. 4:22 Try different things. I really like it. I think it's really cool to allow text to go from one sheet to another sheet. 4:28 All right. Enjoy.</div>