Data Validation Lists

Quick reference

• 00:05 >> One of the most important things that I actually encourage people to do when
• 00:08 they're building models.
• 00:09 Is to try and prevent getting garbage into your model, because if you get garbage in,
• 00:13 you get garbage out.
• 00:14 And data validation is a key piece of that, now working with dates,
• 00:17 working with things like numbers, if we wanted to select our quantity.
• 00:22 Go to Data, Validation, and say, hey, I want to make sure that
• 00:26 this is always a whole number and it's always greater than 0.
• 00:31 This is pretty easy stuff to set up, but what if you want to make sure that you're
• 00:36 only getting valid entries for these particular items?
• 00:39 Cuz the challenge I have right now, is that somebody could come in and
• 00:42 type in Muffin.
• 00:43 And it's gonna accept it,
• 00:44 which obviously my VLOOKUP is now returning a not-found a value error.
• 00:48 I'd rather not see this, so
• 00:50 how do I actually force this to choose from only a valid list of items?
• 00:55 And the answer again is we use data validation, so here's what we're gonna do.
• 01:00 We go to Data validation, Data validation and we'll choose to work with a list.
• 01:07 Now before we get started on this, you'll notice there's a couple of options.
• 01:11 We've got Ignore blank, what Ignore blank is for is it's to say,
• 01:16 these blank cells in here, these can be perfectly valid.
• 01:18 So don't trigger the rule and throw an error.
• 01:21 If we want to force something to be in place,
• 01:23 we need to seed the cell with the value first, like Butter Tarts here.
• 01:27 And then we would uncheck this box, and
• 01:29 at that point if somebody tries to clear the cell out, it'll throw them an error.
• 01:33 So it's a nice little option that we have to change things up.
• 01:36 You'll also notice that we have the box checked for an in-cell drop-down,
• 01:39 you're gonna see where this comes into play in just a second.
• 01:42 Now how do we actually work with our source?
• 01:46 There's two different ways,
• 01:48 we could type in the name of valid items like Bread, comma.
• 01:52 I think we have Coffee Cake, comma, and
• 01:56 then we could go with something like Butter Tarts.
• 01:59 Open parenthesis, 6 pack, because this has got to be exactly right, and
• 02:04 then every comma, we can continue the list.
• 02:07 When I say OK, you'll notice that now, if I go and
• 02:11 click on one of the cells, I can choose Coffee Cake, or I can choose Bread.
• 02:16 That's great, but what happens when the list gets bigger?
• 02:20 Well, now I need to come back into Data validation and go back to the settings.
• 02:26 And I need to start working in this tiny little window here to try to make sure
• 02:29 that every item is in there.
• 02:30 And I got to retype and spell it correctly, it's kind of nasty.
• 02:33 I mean that is kind of the point of why we're doing this because spelling is
• 02:36 hard when you're working with things, right?
• 02:38 So this actually gives the user the ability to pull it off the list, but
• 02:42 I'd rather do something different here.
• 02:44 I'd rather not actually have it based off of a hardcoded list,
• 02:48 I'd rather just choose the list directly from my worksheet table.
• 02:52 Notice that even though tables have a named component to them,
• 02:56 it is giving me a hardcoded absolute reference.
• 02:59 And when I say OK now,
• 03:00 you'll notice that it absolutely has every single item, which is excellent.
• 03:06 There are some challenges still though, I could come in here and
• 03:09 I could start typing in BR.
• 03:10 And you'll notice that it actually goes to autofill with Bread, which is great.
• 03:15 But I don't think I've ever used cookies, so
• 03:17 let's try that, C-O-O and nothing happens.
• 03:22 Data validation does not support hot typing unfortunately.
• 03:25 The reason why Bread came up is because Bread already exists
• 03:28 a little bit higher up.
• 03:29 So that's one of the shortcomings of data validation, now here's another.
• 03:33 What would happen if I went and added a new item like muffin?
• 03:37 And I said this one is gonna be worth $5,95,
• 03:40 it's a really good muffin, will it show up?
• 03:43 Remember, this was an absolute reference, and the answer is, it actually does.
• 03:49 But only because the table is on the exact same worksheet as
• 03:54 the data validation list.
• 03:55 If this table was on a different worksheet,
• 03:57 this would actually cause a problem and it wouldn't work.
• 04:00 So when you're working with the table,
• 04:02 if you wanna feed it into a data validation list.
• 04:05 What we recommend you do is this, we'll go to our products table to the products
• 04:09 column, and we'll create a named range.
• 04:11 I'm gonna call this Productlist, boom, nice and easy, so
• 04:15 how can I check that that's actually working?
• 04:18 Well, go to the Formulas tab, look in the Name Manager, and
• 04:21 you'll see that here's my Productlist.
• 04:23 And notice that it is pointing to Products, Products,
• 04:26 Products being the name of the table.
• 04:27 And Products being the first column within the square parentheses.
• 04:31 At this point, when I say Close, no matter if this is on the same worksheet or
• 04:35 a different worksheet.
• 04:36 If I now go and add something like Donut for a $1.95.
• 04:40 At this point,
• 04:41 no matter which worksheet this table lives on, you'll see that it will come in.
• 04:45 So that's a handy trick that we recommend using when you're actually
• 04:47 setting this up.
• 04:48 Remember, data validation lists are fantastic for
• 04:51 giving a user a way to pick off a preceded list of items.
• 04:54 So that you get valid into your model.

Lesson notes are only available for subscribers.