Data Validation Lists

Quick reference

• 00:04 In this video, we're going to look at a specific type of data validation,
• 00:08 which is called data validation lists.
• 00:10 And the idea here is that I'd like to be able to fill in this form so
• 00:13 that I can actually pick valid products off of my list, and only valid products.
• 00:19 Now we can do this by going to Data Validation, Data Validation, and
• 00:24 we can choose to create a list.
• 00:26 Now the first way that we could do this is we could actually type these values in.
• 00:31 So, I could say, Bread, Coffee Cake, for example,
• 00:36 and you'll notice that I'm gonna ignore blank, so that's important if we don't
• 00:40 mind if the cell is blank at all, and I'm gonna provide an in-cell drop down.
• 00:44 When I say OK, you'll notice that when I select this cell, I get a little drop down
• 00:48 box, and it allows me to choose from the two valid items.
• 00:51 And there's a formula in the cell that's automatically populating.
• 00:54 Now that's cool and everything else, but
• 00:56 I don't really want to have to hand type this list.
• 00:59 And the other thing is, is that it's not gonna allow me to put in bread right now
• 01:03 because that's not a valid entry either.
• 01:06 So you need to remember when you're filling this out
• 01:09 that these are case sensitive when you're working with data validation,
• 01:13 and that's gonna be a little bit of a challenge.
• 01:14 So if I'd like to actually have bread, or bread accepted,
• 01:17 I would have to do something like this, which is a little bit frustrating.
• 01:21 The other alternative that I'd like to do though,
• 01:24 is I'd actually like to provide this with the list directly from this cell.
• 01:28 Now, I could go back and say, let's go into Data Validation and
• 01:33 instead of using these values, I'll just go and
• 01:37 select from this set of cells, and say OK, and OK.
• 01:41 And you'll notice that, indeed, we get all of our items, which is great,
• 01:45 cuz now we just get valid items.
• 01:47 That's very cool, but there's something about this that I'm not really a big fan
• 01:51 of, and that is that we've actually used a hard coded absolute reference.
• 01:56 This is a table, and wouldn't it be nice if we could refer to that column of
• 02:00 the table instead, because remember tables automatically expand as we add more data.
• 02:05 This doesn't, and I don't really wanna have to come back and
• 02:07 manipulate my data validation all the time.
• 02:10 So I know when I look at tables, that I could equals or say equals,
• 02:16 select this and it'll actually create this formula that goes with products,
• 02:19 the name of the table, products, the name of the column.
• 02:23 Well I'm going to go and grab this right now.
• 02:25 So say CMD + C.
• 02:27 Copy that one.
• 02:27 I'm gonna go back to my data validation list, and I'm gonna type in that formula.
• 02:34 Here we go, products, products.
• 02:38 And it tells me that it's not a valid formula.
• 02:40 We'll that's a little bit frustrating.
• 02:42 So let me show you how you work around this and this can be really,
• 02:44 really handy for working with tables.
• 02:46 It is kind of silly though that you have to, but if we go to formulas, and
• 02:51 go to define name, you will notice that there are a few names in this workbook.
• 02:54 One of them is products table, the other one if for the practice and solution, but
• 02:59 if I were to click plus and I was gonna say, let's create one called
• 03:04 DVAL list, for data validation list, underscore products.
• 03:11 Select the range of cells, I'm going to say CMD + V,
• 03:13 we'll put in products products and we'll say OK.
• 03:17 Before I do that, I am just going to go and copy this.
• 03:22 If I now go back to the data validation, I can use the named range.
• 03:31 I can't use the name of the table, which is frustrating as I say, but
• 03:34 I can use the named range.
• 03:37 That named range evaluates to the table and
• 03:40 allows me to actually work with this, so that's good.
• 03:42 Now, next question that you might want to know here is,
• 03:45 how do I copy my data validation settings across from one place to another?
• 03:50 So what I am going to do here is I've got a couple options,
• 03:54 I can grab the entire range and go to data validation and
• 03:57 it says, hey, you've got some cells here with out data validation,
• 04:00 would you like to extend the data validation to all of these cells?
• 04:03 I can say yes, that's one way that I could do it.
• 04:06 The other way is I could actually copy the cell,
• 04:10 grab the rest of the cells, right click, say paste special,
• 04:15 and I could actually paste the validation settings.
• 04:20 That would also work, and you'll notice now as I select these individual cells,
• 04:26 that I can click them from the drop down list as well.
• 04:30 One last thing that you want to know about data validation is how to get rid of it if
• 04:34 you don't really want to have it anymore.
• 04:36 The way to do that is to select the cell or cells that you don't want your data
• 04:41 validation on, go back into data validation,
• 04:43 and the option there is to clear all, and that will clear the data
• 04:47 validation from the cell that you're actually selecting at that time so.
• 04:51 So cool little trick.
• 04:52 Great way to actually make use of things no matter where you're table is sitting.
• 04:55 If it's on a different worksheet or not, after you name a range to it you'll be
• 04:58 able to pull it in and I highly recommend that if you're trying to send something
• 05:02 out that you want people to fill in from defined lists.

Lesson notes are only available for subscribers.