Data Validation Lists

Quick reference

• 00:04 In this video we're going to look at data validation lists as a feature to prevent
• 00:09 garbage from getting into our model.
• 00:11 And therefore, messing up our end results.
• 00:15 If we look at this particular form, we have a form for
• 00:18 a bakery that's trying to trade maintain holiday treat orders.
• 00:21 So they're taking pre-orders of different things from their customers and
• 00:26 the way it works is that we've got a table of products with prices over here.
• 00:29 We're using a VLOOKUP formula nested in the middle here to look up the appropriate
• 00:34 price for whatever product's been thrown in column A.
• 00:37 And that formula runs all the way down this table at which point
• 00:40 the value will multiply the price by the quantity and total it up for us.
• 00:45 Now, what we want at this point in time, is we want our users to come along and
• 00:49 type in the name of the products that they're looking for.
• 00:52 So coffee cake, they might wanna get one of those and everything would work great.
• 00:57 But what if they decide to come along and put in something that
• 01:02 doesn't work like sticky coffee pudding that is not even spelled correctly.
• 01:08 Right now, our formula returns not found.
• 01:10 But this isn't ideal, we would really rather force the user so
• 01:15 that they can only enter these particular items.
• 01:18 We've got a couple of ways of doing this.
• 01:21 I'm gonna select all the input cells right now.
• 01:24 And I'm gonna go to data validation.
• 01:25 This is on the data tab.
• 01:27 We're gonna go data validation.
• 01:28 And you'll see that we have the option to provide a validation criteria.
• 01:32 And one of the things we can allow is a list.
• 01:37 Now, I could type in the items that I want,
• 01:42 like coffee cake and sticky toffee pudding.
• 01:48 And I could hit Enter.
• 01:49 These are comma separated.
• 01:50 And what you'll see is that when I do this, I get, as soon as I click on one
• 01:54 of the cells in that area, I get this little drop down arrow.
• 01:58 And you'll notice that it gives me the values that I put in separated by commas.
• 02:02 So here we go,
• 02:02 sticky toffee pudding, if I choose that now it'll actually validate correctly.
• 02:06 Because I've got this typed in the same as what was over here.
• 02:10 But we obviously have a lot of different items over here, and
• 02:13 I really don't want to have to go and type in each one of these separated by a comma.
• 02:17 That's an awful lot of work.
• 02:19 If I'm trying to only do two or three different things,
• 02:22 like yes or no, things like that, then it might not be a big deal.
• 02:26 But if I'm trying to do a whole big product list,
• 02:28 it's going to be a bit of a nightmare.
• 02:29 So what I'm gonna do is I'm gonna go back and gonna select all these cells here.
• 02:33 I'm gonna go back to data validation.
• 02:36 And at this point, instead of using this comma separated list,
• 02:40 I'm gonna delete this.
• 02:42 And I'm gonna go click the little ref edit box here.
• 02:44 And on my worksheet I'm going to select all of the products that are in
• 02:48 my table there.
• 02:50 We'll say OK.
• 02:50 And it gives me the range of F4 to F8.
• 02:55 Now we can say OK.
• 02:56 And what you'll see now is that when I go and
• 02:59 grab the next line it's picking up all of those products.
• 03:03 So I can order butter tarts and I could order some cookies.
• 03:06 And all the prices are coming in so now as I go and, and use these items
• 03:12 They're all going to extend just fine.
• 03:14 So that's really kinda nice.
• 03:17 Now a trick that you're gonna want to be aware of is that if you try and
• 03:20 use this table and move it to another page
• 03:23 Your data validation may not work properly and whatnot.
• 03:27 The best thing to do is to actually refer to this area,
• 03:31 instead of using F4 to F8, is to actually give it a name.
• 03:35 And that way we can be sure that we're always targeting back to the portion of
• 03:38 the table that we need.
• 03:40 So, the name of this particular table, we can see, is Products,
• 03:44 by looking at the table tools.
• 03:45 And we can refer to this first area here,
• 03:48 and if we look at it in the formula, I can say equals and select this.
• 03:52 And you'll notice that it comes back with ProductsProducts.
• 03:55 So, this is sort of a name.
• 03:57 I'm gonna go and Ctrl+C to copy that and then just Escape from here.
• 04:02 Now what I'm gonna do is I'm gonna go to the FORMULAS tab.
• 04:05 And I'm gonna go to the Name Manager.
• 04:08 And I'm gonna say New.
• 04:10 And we will call this one, and
• 04:12 I always like to to start off with something like dval
• 04:15 And it's just a, a, a shortcut term that I use so
• 04:19 that I can look this up elsewhere and know what it is.
• 04:21 And I'll call this dval products.
• 04:25 And that's just a name that I'm gonna use somewhere.
• 04:27 And in the refers to area,
• 04:29 I'm gonna copy in this thing that I pasted, ProductsProducts.
• 04:33 And at this point I'll have a name that points to that range.
• 04:35 So we'll say OK.
• 04:38 And you'll see that we have it listed here Dval Products and close.
• 04:41 And you'll see that now from the name box I can quickly jump to it and
• 04:45 it will select that for me as well.
• 04:47 The beauty of this is that now I can go back and I can go into my data validation.
• 04:54 And I can rename this guy here to dval_products.
• 04:59 So this will always be pointed now, at this list.
• 05:02 So if I go and add something like bread, for
• 05:06 $3.95, at this point, you'll see that it now shows up.
• 05:12 And this will work no matter which worksheet this particular table
• 05:16 is actually on.

Lesson notes are only available for subscribers.