Data Validation Part 2

Quick reference

• 00:04 More on data validation.
• 00:06 As promised, let's look at the last two data validation examples from our file.
• 00:13 The first one is for Cell E8.
• 00:16 And we've got to make it so that the only inputs allowed here
• 00:20 All those were the text lengths between two and four characters.
• 00:24 This is surprisingly simple.
• 00:26 Alt + DL, you'll recall the keyboard shortcut for data validation.
• 00:31 And all you have to do is find the right allowance,
• 00:34 it's actually text length, minimum of two, maximum of four.
• 00:39 Whenever you see these buttons, this can act as cell reference.
• 00:42 For instance instead should you wish.
• 00:44 Click OK.
• 00:45 If I type in Liam, that's four characters.
• 00:48 It's okay.
• 00:49 But if I type in Liam with a space, that's not.
• 00:53 That's one of the reasons we use drop down boxes,
• 00:56 because people often will put spaces afterwords if they're touch typists.
• 01:00 This is a way to sort of To prevent that.
• 01:02 And also to stop problems with forms if characters become too long.
• 01:08 This is what data validation does.
• 01:11 It stops you having to write more complicated calculations for
• 01:15 formulas later.
• 01:16 If you've got a formula that's gonna cause a problem if the input value is zero.
• 01:21 Why not prevent someone from putting a zero in in the first place?
• 01:25 That's the whole idea.
• 01:27 Now, the even number only at the end,
• 01:30 well, we've actually looked at even numbers once before here.
• 01:34 You may recall I said I was going to talk about the MOD function properly,
• 01:37 and I will.
• 01:38 What MOD does is it looks at the remainder after you divide a number.
• 01:43 So 3 divided by 2 is 1 remainder 1.
• 01:46 So the mod of 3,2 is what?
• 01:50 Its just it's the remainder.
• 01:52 So an even number is that the remainder is zero after dividing by 2 and
• 01:56 that's what I am going to work on here.
• 01:58 So 'Alt' 'D' 'L'.
• 02:01 I'm going to go here.
• 02:03 Well I can't go whole number,
• 02:04 because there isn't something here that allows me to go for equal, doesn't work.
• 02:08 So I'm gonna have to go down to the bottom, custom.
• 02:12 And I'm going to put in here equals the amount of what I want.
• 02:15 Well I'm in Sally ten so it's going to be ten.
• 02:19 And, it is divided by two, has to be zero.
• 02:25 So there's no remainder.
• 02:27 Then it will work.
• 02:28 If I click okay, if I put 17 in here, it won't work.
• 02:34 If I put 1.999 It won't work.
• 02:39 If I put two in, it's happy.
• 02:41 If I put 26 in, it's happy.
• 02:44 How simple is that?
• 02:47 What you should try and do is when you're actually building a model,
• 02:51 not only should you be considering likely text, units and actually formatting it.
• 02:57 You should be considering.
• 02:58 Should I put any data validation in as I go?
• 03:01 I'm defining it there and then.
• 03:03 It'll make formulas that are dependent on these inputs so
• 03:07 much simpler as a consequence.
• 03:09 It really is easy.
• 03:12 Practice with it, it's very useful.
• 03:15 And unlike Conditional formatting data violation does move if
• 03:19 you start inserting rows and columns.
• 03:21 So it's not so much of a problem.
• 03:24 So I've now got data validation sorted out.
• 03:27 I've got conditional formatting.
• 03:30 I'm in a position now where I start thinking,
• 03:32 okay I've got to create a template working here.
• 03:34 Perhaps now I can consider the model integrity, I think about checks.
• 03:39 And that's what we're going to look at next time.

Lesson notes are only available for subscribers.