All language subtitles for 3. Custom Data Validation

In the previous couple of lessons, we've been taking a look at data validation, and we're going to

finish out this section on data validation by taking it up a notch and looking at some of the types

of things that you can do with custom data validation.

And this does get a little bit complex, but it also gives me a chance to show you some functions that

we haven't looked at yet.

So what are we trying to do here?

Well, I have an invoice number in Cell A7 and this invoice number and all invoice numbers that are

going to be input into this spreadsheet must have two letters at the start, followed by four numbers.

So we want to create a data validation role which checks the invoice number that's been entered into

the cell to see if it meets these conditions.

So two letters followed by four numbers, giving us an invoice number, which is six characters in total.

So how can we do this using data validation?

Let's jump up to data and go into validation.

What we want to do here is we need to select custom because when we select custom, we can add a formula

to this cell and that's exactly what we need to do.

Here, we need to come up with a formula which is going to check the invoice number against these conditions.

Now I could start typing the formula straight into this blank field just here.

However, that isn't my preferred method.

The problem with type in a formula into here, and I'll just show you an example with an F is that you

don't actually get to see the function arguments underneath.

And me personally, when I'm putting together a formula, particularly a more complex one, I like to

be able to see those arguments.

So I like to construct the formula in the worksheet first of all and then copy it into that formula

field in that data validation area.

And the formula that we need for this is quite a long one.

So we're going to break it down so you understand exactly what we're doing.

What do we need to do first here?

Well, what do I know about the invoice number that's going to go into this cell?

Well, I know it has to be six characters long, so I'm just going to type lengths of number just above,

and I'm going to put a formula into this cell, which is going to tell me what the length of that value

is, and we're going to use the LEN function to do that.

It only has one argument, and that is whenever the invoice number is located, let's hit enter.

So this is telling me how many characters I have in the value in Cell A7 now for the type of formula

that I want to construct here.

I need these calculations to produce true or false results.

So what?

I'm basically going to do here to get this to display true or false instead of the number six is I'm

going to basically turn this into a logical formula in the formula bar and we're going to say equal

to six.

So the formula is going to grab the number of characters in the string and then it's going to test it

and see if it's equal to six, if it is, it's going to give me a true result if I was to add another

character onto the end.

It's going to give me a false result.

So now I have the true or false argument that I need for the first part of this formula.

The next part of the formula?

Well, let's take a look at what we need here.

We want to check that the first two characters in the invoice number are letters.

So let's say here first to characters letters.

Now, how do we extract characters from a string?

Well, we did this previously.

We can use the left function.

I want to extract from this piece of text and we want the first two characters.

Let's enter.

So again, this is going to extract the characters in this case.

See B.

But again, I need a true or false result in here.

So what I'm going to do is something a little bit different.

We're going to go up to the formula bar and we're going to use the function is no, I'm going to wrap

that around the left function.

And what this basically does is it looks at what we have in the cell and it says, is it a number true

or false?

So this is telling me that the first two characters are not numbers.

Now let's just test this.

If I was to change the first two characters to one and two, that should change to false, but it's

not.

So there's something not quite right.

Just here.

Let's put this back to UCB.

Now, if we go in and evaluate this formula, we're going to be able to see what's wrong with this formula.

So what I'm going to do here is I'm going to evaluate this formula and just quickly step through it.

So let's click on Evaluate and I can see what the problem is.

So where we have the actual invoice number, but notice that it's in quote marks.

So that means that Excel actually thinks this number is text.

So we need to add another little function, which will convert text to a value.

So I'm going to click after is no, I'm going to use the value function.

Let's hit, enter and see what we get.

Well, we're getting false.

Now, this should be a true if I go to this invoice number and if I change this to numbers instead of

letters, it then goes to true.

So it's kind of doing the opposite of what I want it to do at the moment.

So what I'm going to do is I'm going to go up to my formula and we're going to add a knot function in

front.

And this is another logical formula.

And this is basically going to get it to flip around and do the opposite of what I want it to do.

And now we have another true condition.

We're kind of aiming for all of these to be true when the invoice number is correct.

So now all we need to do is very similar to what we've done previously.

We just don't need to add the knock condition for the last one.

So we're going to extract the last four numbers.

Let's type in equals, right?

A text is in cell eight seven, and we want the last four characters, as you might expect, that's

going to give us eight eight, six five, so I'm going to go straight up to that formidable.

We're going to say is the number and we're going to convert it to a value.

Let's make sure we close off as many brackets as we've opened.

And now we're getting a true result.

So before we copy this formula across to data validation, let's just make sure that these are working

correctly.

So the length of what we type in should be six characters if we type in additional character that switches

to false, which is incorrect.

The first two characters should be letters, so if we change these to numbers that will switch to false,

which is incorrect, so we're looking good so far, the last four should be numbers.

So if I change these two letters, that one is going to switch to false.

So I can see the outside of this data validation.

This is all working, OK?

So what I basically need to do is take the three formulas that I've created and combine them together,

and I'm going to do that using an end function.

Now we've got quite a bit of copying and pasting to do so.

I'm going to open up the clipboard and get that to do a lot of the hard work.

So I'm going to double click on the first formula.

Let's select it, but don't select the first equals control C to copy it to the clipboard.

Let's double click and let's copy the second formula.

Control C to copy it to the clipboard.

Let's double click again.

Select the third formula.

Control C to copy it to the clipboard.

So now that I have these in there, it's going to be very simple for me to combine them all together

using an end.

So we want the first one comma, then the second one comma, then the third one close up another bracket

hit enter and a formula isn't producing an error.

So I think this looks pretty good.

Now that we have this big, long formula in the formula bar.

We can copy it from here and now we can add add data validation.

So let's click on the cell.

Up to date validation.

And this time we're going to go to custom and we're going to control V to paste that formula in.

Let's click on.

OK, so now that we've done that, we can get rid of all of our calculation cells.

So if somebody was to now come to here and enter in an invoice number that wasn't valid, we should

get an error message.

So if I was to enter a one two three four five six, that's not valid.

Firstly, it's too long and I only have one letter at the beginning.

What about if I enter something like K L eight nine zero one?

That one's absolutely fine because it meets all of the conditions.

What about if I was just to add some random numbers on the end, I'm going to get an error message again.

And of course, if you wanted to, you could set up your own custom alert, custom warning and custom

error message.

Now that formula is really long and pretty complicated.

We've introduced quite a few different functions in that.

And if it is a bit too much at this stage, don't worry about it.

I just wanted to show you something a little bit more complex and also get you working with some formulas

that you're not familiar with.