This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
About this Tutorial
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.
Better Than Happy | Redesign of The Feelings Wheel
How Starter Story Designs Data
Learn to Love Your Sheets
Best Header Font Ever
Sheet Review! 150 Active VCs by LemonIO
Anika Asks: How To Set Text Overflow All The Time
Introducing: Brutal Calendar
Add a Checkbox to Turn on Dark Mode
Create Drop Shadows! This makes your dashboards pretty.
Merge Cells for Dashboards
10 Things I Hate About Your Spreadsheets
Dark Mode / Better Font Color
Better Font Colors
Magical Things You Can do with Checkboxes in Google Sheets
How To Export Your Beautiful Sheets to PDF
Consider Labels as Opposed to Headers
Add Icons To Your Sheets With a Domain Name
How To Color Cell Blocks So Others Enter Data Easily
Great Sheets! Corona Hiring Sheet
Great Sheets! Community Information Board by Seedtable.com
Roast: Hotel PPC Channel Cost Calculator
Better Header Fonts - Best Fonts To Use In Google Sheets
Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell
Basic Keyboard Shortcuts To Speed Up Your Productivity
Basics - 5 Ways to Change Row Height
Anders Asks: Can I Highlight Whole Row if Certain Columns have text?
How to Refer to Other Cells - A1 and R1C1 Explained
Change the Default Font
Biggest Flaw In Dashboards with Dark Colors
Basics - 4 Ways to Change Column Width
Basics - Structure of a Sheet: Index() Row() and Column()
Communicate Better with Gridlines, Border Styles, and Border Colors - Google Sheets
Use Cmd + Y To Do It Again, and Again, and Again
Create an Auto-Update Sales Chart: Trailing 12 Months
Google Sheet Basics - The Absolute Basics
Secure Your Sheets by BetterSheets.co
How To Create An AutoFill in Google Sheets
Build a Thermometer for Savings Goals
Make Your Lists Spicy Hot in Google Sheets
Restrict Access to a Cell if Another Cell is Blank
How to Use Smarket
Combine Data from a Tab and a Totally Different Sheet | ImportRange and Curly Brackets!
Job Application Tracker Template | From TheLandOfRandom - Sheet Improvement!