In the previous videos,
we saw how we can use data validation to both prevent errors and speed up data entry.
Both of these were important, but they're quite basic.
When problems get a little bit more sophisticated,
we can actually include calculations in our data validation as well.
And we're going to now look at how to do that.
For example, if you have a look at our last order date,
that obviously has to be a date,
which is one thing we could validate for.
But it also has to be a past date,
in other words, a day that doesn't occur in the future.
In order to add this to our validation criteria,
we will need to use a function.
And in this case, we'll use the TODAY function to help us.
As before, we need to select the data we want to validate first.
And then I'm going to come up to my Data tab and click on Data Validation.
What we want to allow for this time,
is a type of date.
So I'm going to click on Date.
And I don't want it to be between two values.
I want it to be less than or equal to today's date.
But the problem is, that I can't just type the end date here.
I need a calculation to tell me what today's date is today.
In other words, at any time when the validation occurs.
Remember, all calculations begin with an equals.
So we type equals and then the function TODAY,
open and close brackets.
So we still follow all the rules of working with functions and formulas.
Now, press Enter.
Let's check whether our validation works as we expect it to.
I will add a new entry at the end here.
Now I'm doing this on the first of September.
So if I type first of the ninth,
I would expect that to be valid and yes, no problem.
If, however, I enter the second of September,
in other words a date that hasn't yet occurred,
it should be rejected.
And there's my error message. Excellent work.
So now we've seen how we can actually use a calculation in our data validation.
But that was quite a simple one.
Let's have a look at one that's slightly more sophisticated.
The product codes in our database need to be unique.
In other words, Alex wants to make sure that these are not duplicated by accident.
So we're going to look at how we can use
a COUNTIFS function to avoid people entering duplicate product codes.
We saw in the previous videos,
that using a table helped increase our automation
as the table and data validation grow with our data set.
As we start adding calculations to our data validation,
we want those calculations to extend the range they're looking at,
as the data grows.
And we can do that very easily by using
a combination of tables and named ranges in our actual data set.
Now our data set is already in a table, which is great.
But let's name the product code column.
Click twice to select the data and the heading,
then come up to my Formulas tab.
And click Create from Selection, and then okay.
Now we're all set up and ready to go.
So first, we select the cells,
then come up to the Data tab,
click Validation, change that to Custom.
And then in the formula,
we're going to type =COUNTIFS, open our bracket,
now remember, the COUNTIFS function,
the first thing it wants to know,
is the range you want to check.
Now that's going to be our Product Code column.
And here we will use our named range.
Then type a comma,
and what we're checking,
is the number of times this first value occurs in the Product Code column.
So I'm going to click on BATH-020.
Now obviously, we're typing what it will do for the first value,
but it will now go and apply the same formula to all the other values.
Now it's time to close our bracket,
and we're checking that that is less than or equal to one.
If it is more than one,
it means we've got a duplicate.
So that's the formula.
You can now see why we've named the Product Code column.
Because even as we add data to it,
because it's in a named range in a table, it will grow.
If you use a specified range,
the moment you go outside of that range,
you're going to find this formula starts giving you problems.
So the named range is really the way to go.
As we have discussed before,
it is good if we set up an error message or
an input message to assist users with entering valid data.
So I click on the Error Alert tab,
there's already a message from a previous step.
I can change it to be relevant to my new validation.
Let's stick with a Title.
We change the style to Stop,
because I don't want to have any duplicated product code.
The other two options will let the user enter invalid data just by
giving Warning or Information which we
really don't want in the case of the product codes.
And then we'll change the error alert to: Duplicate product code is not accepted.
And once you're happy,
click OK. And now,
very importantly as we've done before,
let's go test that it works.
So I'm going to come to my second value here,
and I'm just going to change that to 020 and try and click away.
And now you can see,
it's not allowing the duplicate. Job well done.
I'm going to click Cancel now and let's check that it will
still work when we add a new value at the bottom of the table.
So I'm going to start by adding one that I know is unique.
So I'm going to type in MATE-999 and press Tab.
And it's not complaining about that, which is excellent.
Let's check that MATE-091 does give us an error,
which it should, and Tab,
and here's our error message.
So we can now see that that data validation is not only applying to the existing data,
but will extend to new values that we add as well,
thanks to our data being in a table.
And if we check our table on the far right side,
you will see that has come down.
And all the other data validation has extended as well.
So when I check my Location for example,
there's my drop-down list that we created earlier.
So we have now seen how we can use calculations to make data validation more powerful.
And also, how by combining tables and named ranges with data validation,
we get that great level of automation as well.
In the next video,
we will look at some more tables for working with cells that have
data validation added to them. I'll see you then.
[SOUND]
Dr Yvonne BreyerDirector, Learning and Teaching
