Data Validation

Quick reference

• 00:04 So we've got a cool chart that allows us to just change one cell and
• 00:08 restate the entire thing.
• 00:11 The problem we have here though, is that when we try and deploy this to our users,
• 00:14 they now have to try and
• 00:15 remember which category is it that they're actually trying to work with?
• 00:19 So, where they might be looking for sleeping bags, they'll go and
• 00:22 they'll type in three hoping to get close and realize shoot it's the wrong one.
• 00:26 It must have been two.
• 00:27 All right, there we go.
• 00:28 Now I've got the right one.
• 00:29 So that's not really the best user interface experience.
• 00:32 Wouldn't it be better to provide them with some kind of other piece like a spin box
• 00:37 or a drop down list that would allow them to choose the right value the first time?
• 00:40 And as it turns out we can do both.
• 00:43 In this module, we're going to focus on the drop down list.
• 00:47 Before we get started, we need to do one kinda weird little thing here though.
• 00:51 Remember my table?
• 00:52 If we go and click from the drop down list,
• 00:54 we can select product lines, well the first thing I'm going to
• 00:57 do before I go to set up my data validation list is I'm going to do that.
• 01:01 And then I'm going to go and create a name by selecting product lines here and
• 01:05 typing in D.V. products.
• 01:07 D.V. for data validation.
• 01:10 Interestingly enough, it shows me again that I have product lines, but
• 01:13 if I go back and choose D.V. products, I go to the same place.
• 01:17 Why would I do that?
• 01:18 Well here let me show you.
• 01:19 Under the formulas tab, under name manager, you'll see product lines refers
• 01:24 to this source date area, D.V. products the first two product lines product lines.
• 01:30 That seems silly.
• 01:31 It seems like a duplication.
• 01:32 But unfortunately, it's something that's necessary to work with the data properly
• 01:36 in the data validation area.
• 01:39 Now, we're going to select one of our sleeping bags.
• 01:42 And you'll notice we have a formula here.
• 01:44 We're going to forgo this.
• 01:45 We're going to go to the data tab and I'm going to find Data Validation.
• 01:51 Now mine is a small shrunk down button, because my ribbon is a bit compressed.
• 01:54 Yours will probably be full size.
• 01:56 Regardless, when you click on it you'll be taken into this particular window.
• 02:01 Now inside the Data Validation area you'll notice that the defaults is to allow
• 02:05 any value for a cell.
• 02:07 And this makes sense, because when you create a brand new worksheet you don't
• 02:10 want to have any restrictions on what can go into those cells.
• 02:13 But we have the ability to toggle that.
• 02:15 So now when I click on this, you can see that I can set and force someone to enter
• 02:19 a whole number or a decimal number, dates, times, things like that.
• 02:23 What I want to focus on is a list.
• 02:27 Now, you can set up a simple list really easily by making a comma separated list.
• 02:31 So, I could type in each of my individual items, separated by commas, and
• 02:35 that would work, but that just seems kind of mundane.
• 02:38 I don't really want to do that.
• 02:39 Instead, what I'd like to do is select my area of data.
• 02:44 And this is where I'm saying that we have this bit of an issue.
• 02:47 This pulls back this hard coated range.
• 02:49 It doesn't pull back the product lines reference.
• 02:52 And this is why I set up my nice little list for DV products, so
• 02:57 that I have a list that points back to the tables name.
• 03:00 And that way it will always expand.
• 03:02 I have options I can set.
• 03:04 Ignore blank, and install drop down.
• 03:06 I'll leave both of those as default for right now.
• 03:08 And now we'll say okay, and what you'll see is that we get a drop down list.
• 03:13 When I go and click on this little arrow,
• 03:14 you'll notice that it gives me a nice list of all of my items.
• 03:17 And I can now pick Tarps and Groundsheets, or I can even,
• 03:21 if I want to, type and hit enter, and it will still allow the correct input.
• 03:27 On the other hand if I were to go and try and type in something else, like cats,
• 03:31 it's going to come back and give me an error that says you can't do this.
• 03:35 You can retry as many times as you want, but I've got to tell you the cats is not
• 03:38 changing and therefore it's not going to work.
• 03:41 The only option that really works is cancel,
• 03:42 because help actually takes you to a page that doesn't offer any help whatsoever.
• 03:46 So we'll hit cancel here.
• 03:48 Can we do better than that message though?
• 03:50 Absolutely, let's go back into our Data Validation, we'll
• 03:54 select our Data Validation, and you'll notice that we actually have three tabs.
• 03:59 We have an Input Message, and this can show up every time you select the cell
• 04:03 with a little yellow box to tell people what to do.
• 04:05 I find in my experience that users get really tired of this really fast, so
• 04:10 it's better to just give them a lesson in person, and
• 04:12 then you don't have to worry about this particular piece.
• 04:15 But the part that I do like to send is these guys here.
• 04:18 We have error alerts.
• 04:20 Error alerts can be configured to be a stop style, a warning, or
• 04:25 an information style.
• 04:27 The difference is that information will pop up and
• 04:29 tell you what's going on and allow you to clear it.
• 04:32 Warning will pop up a message and say are you sure you want to proceed.
• 04:35 So you get the option to yes, no or cancel.
• 04:38 Stop completely prevents a user from putting in the wrong thing.
• 04:43 So in this case here, I would say let's put in something that says invalid data,
• 04:47 and we give a nice little error message that says,
• 04:49 please choose a value from the drop down list provided.
• 04:55 Something to give them a little bit of help, and now when we say okay,
• 04:59 what we'll find is when they go and select from the list, sleeping bags, it works.
• 05:04 If they type in something like tents, but if they come in and try and
• 05:09 put something else in there, like Cats, they'll get my custom error message
• 05:13 that tells them to please choose a value from the drop down list provided.
• 05:17 So this is a really useful function for driving your dashboards, because people
• 05:21 can type and they can select from a drop down list without getting it wrong.
• 05:25 Meaning that you don't have to worry about data that's being incorrect
• 05:28 entered into your spreadsheet.

Lesson notes are only available for subscribers.