0:00 Hey welcome. So Thomas is asking this question of restricting access to a cell. If another cell is blank and I've used this use case here, let's say you have sort of an items you want to list, and you want to make sure that people who are listing items, if, if it's not yourself or it's other people that you don't have necessarily like contact with, you can't necessarily say, Hey, just don't put any notes.
0:24 Unless you have this like item in this list in this case, what you might want, I'll show you what to do.
0:31 Basically. Here's what the end result is. So you have a name of something, you have the cost, and then we have some notes and we can say, Hey, this is an F heavy item.
0:41 Great. That works awesome. And you have another name of something. You have another cost, and then you say, Hey, we need to fix this great works.
0:50 Great. Let's say you have a name and you don't have a cost. And you're like, we definitely need a cost before we put in any notes.
0:56 So watch what happens here. Let's put in some notes and we hit enter and it's like, there was a problem.
1:03 Here's the problem. The data you entered in cell four violates a data validation rules. So key insight here, we're going to use data validation to do this.
1:11 Uh, and if we go on more and we say, Hey, you want to add a note here, cannot do it.
1:19 How do we do this? All right. Here's the inside scoop. What we're going to do is we're going to restrict this.
1:27 I'll show you how restrict this column. I'm going to do it cell by cell, but it'll be easy to copy and paste and get it through everything.
1:35 Let me smell restrict correctly. All right. So we need to sort of pick our battles here. We can do it.
1:43 Any number of ways of like everything. Well, we know like, okay, in this cell B, we were going to get a cost.
1:49 If we have an item and we want C to be restricted until then, that's what we want to do in this particular case.
1:54 So we're going to right click. We're going to go down to view more cell actions, data validation, click on data validation, move my face over.
2:04 Now in our criteria, both one thing I want to point out is that we are using D two, okay? We're in the D two cell and our criteria.
2:16 We want to change this to custom formula. Now it's going to give us this, uh, to fill out. So we're going to do equals.
2:25 Now the first thing I did was is blank. I just put up is blank in here. And I did see two, but this actually ended up being the exact opposite of what I wanted to do.
2:37 So the simple resolution, if we get, if we find out we do a custom formula, we use formulas and we're getting the exact opposite.
2:46 All we have to do is put a knot at the beginning and put this, uh, parentheses around. This is blank.
2:52 Now, right now, I want to show warning. But the other thing I did was reject input. I'll show you that next.
2:58 Uh, but all I want to do is show warning in this particular case, I want to show you that it works.
3:04 Okay? So now we have an item in C. We have a text in C2 so we can use, we can do this.
3:11 No problem whatsoever. Now let me delete that text. Let me copy and paste this down. We can actually do command C shift, command down arrow, and I can command V paste the entire, and now you can't see it, but we have data validation everywhere.
3:30 We can actually right. Click data validation here. And we'll see it shows up and check out this. The cell range that I'm in right now is deep five.
3:40 The one before that was D two, the custom formula changes automatically. So if for a device it's D S a C5 here, this is really helpful because we don't necessarily have to write this out.
3:54 Every single time we can copy and paste, and the rows are going to change. Also, I will show you. Let's see if I take this and move it to copy and paste it to F two.
4:08 What happens there? I'm going to show you what happens. We're going to shut the, see the end results in a second, but I just want to share with you how sort of data validation works here.
4:17 Check it out. <inaudible> So just for warning, if you're trying to use this and you're trying to copy, paste it to other, uh, columns, you're going to run into this issue where it co it actually changes the column and the row number.
4:32 What you can do is add a little dollar sign in front of it to just lock it up. If you wish that's one resolution to that.
4:40 Okay. Let's see if this actually works. Now we go. Hello? Perfect. Hello. Perfect. Hello. And we get a warning. This, uh, little red thing in the corner is our warning cells.
4:57 Content violates its validation rules. Perfect, but we don't want the text actually show up. We don't want just a warning and say, Hey, uh, you couldn't really do this.
5:07 We want to reject every text. We want it to make sure people filling this in know, Hey, you got to go do the other thing.
5:13 You got to go fill in the other thing first. So let's do that to view more cell data validation. And all we're going to do is change it from show warning to reject input.
5:26 We can also actually, let me show you this. I'm going to do one more step after this. Let's save that it's copy paste.
5:35 It will actually delete the text in there, but the data valet should still remain. So we'll paste it all the way down.
5:43 And now we go, hello works. Hello works. Hello, doesn't work. And we get an error. The data you entered in cell D four violates the data validation rules.
5:54 Now this is a very general, uh, notice, right? We want more specific. If, if someone else is using this sheet and we haven't necessarily talked to them, we haven't told them what to do.
6:06 We want the, the error. They get to be very explicit of what do they need to do to fix this thing?
6:12 They're doing something now that's wrong, but we want to share, share with them something that is correct. So what we can do, let's go back to our data validation.
6:20 I'm going to delete the text here. We're going to go back to our data validation. We're going to do one extra thing.
6:27 We're going to show validation, help text. And here it says, enter a value that satisfies the formula and it does this great.
6:36 We don't want that. We want to write in some texts. We want to say, fill in C column first and hit save.
6:45 Now we go, hello? Hello works, works great. Hello. Doesn't work. And that gives us this message, right? Oops. What I did there, I didn't copy paste the data validation.
7:03 So now we have data validation on all of them. Now we go, hello. It works. Hello. It works. Hello. Now, now the message has changed.
7:12 Fill in the C column first. So that very general he data validation rules where you didn't follow them. Now we can give an actual message of fill in the C column first.
7:22 Okay. Now I'm filling this in. I know exactly what I need to do first. All right. I need a fill in the notes.
7:28 I think actually I can't do that because we have our other data validation there. All right, let's go back. We've got to do $6.
7:34 Let's say $6, 600, a pricey expensive can do. Awesome. We have now filled in the C column and now we go, hello there.
7:52 And now we can fill it in. Now this is really cool, a really fun and interesting way to add some steps and warnings into your sheet.
8:01 If you are having sort of difficulties, knowing what order, no. Have people have difficulties knowing what order to fill things in.
8:10 Now you can stop them in their tracks right away and restrict access to a cell. If another cell is blank, this is really fun.
8:16 Hopefully you have some good use cases. If you have a good use case and you find this video, helpful comment down below how you're using this, what you're doing, then if you have any other questions, maybe you have some questions on how to expand this or use this in your sheet by.