- All right, if you're using Microsoft Excel,
you may be finding yourself in a situation
where you don't want to be able to enter values
into certain cells based on the values of other cells.
So a good example of this could be, say a shipping record.
So I made one here and let's say someone comes in
after something is shipped
and they type in a tracking number.
So for example, if this puzzle on May 17th was shipped,
you would come in here,
put in tracking number and hit enter.
All is fine with that, right?
But if it's not shipped yet,
you accidentally put this on the wrong line,
so the shipping status of this item is pending.
What would be nice is when the person presses enter,
it doesn't allow you to input it.
So you can do that, and it's only a couple of steps
and we'll go through a few different scenarios
because everyone's spreadsheet is different, right?
It's not gonna be exactly like this,
but let's do the most straightforward first.
So let's say we're trying to protect the range E3
through E18 from getting a tracking number entered
unless the shipping status is shipped, okay?
So the first thing that you do is just select that range
and go to the data ribbon.
And we'll use data validation, left click here,
and the data validation box comes up
and it starts with the validation criteria of any value.
So that's essentially turned off.
So of course, what you're going to want to do
is change that, let's left click on this dropdown box,
and we're going to just use a custom formula.
So left click on that.
And then you get this box here
that's waiting for your formula.
We're going to write a formula that says,
is value in D3 equal shipped?
If it does, that's okay, otherwise reject the input.
So we'll say start the formula out with an equal sign.
And when you write a custom formula for data validation,
you write it as if it's in the top left-hand side
of the range that you're working with.
This range is only one cell wide so it's just the top value,
but we're going to look for the top value
when we're checking that is going to be D3.
We just want to see if it's equal to shipped.
If it is, the rule validates and you can enter your data.
Left click on okay.
And then we'll act as if we're going to come in here
and accidentally enter a tracking number
for something that's pending.
Hit enter, and it won't allow you,
says it doesn't match the validation criteria
and that's the most basic level.
And we're just going to expand this a little bit
and then talk about some different options.
So let's say we wanted to apply this
to more than one column.
So you have to do the formula a little bit differently.
So the first thing that we'll do,
we'll go clear out that rule.
So we'll left click on validation,
select clear all and click okay.
And now go back and grab this range,
click on data validation.
So let's keep in mind here,
we're still going to do a custom formula,
but whether or not were evaluating the data
in column E or F,
we're still going to want to look at column D.
So when you write the custom formula for this,
the only change is going to be,
you want to put a dollar sign
before the column reference of D,
and then that tells Excel, always look at D.
So when you go into column F,
which is called shipping service,
don't shift over and look at column B, stay in D
and we'll just do the same row.
And we'll say, equal to shipped.
Click okay and then this rule should apply to both.
So let's say it's pending and you go in here
and start, and you just type FedEx ground.
Hit enter, it won't let you do it.
All right, so we've covered both of these columns.
The next scenario that we're going to look at,
let's go to this other worksheet.
All right, so for this spreadsheet,
we'll control this entire range
just based on the value of this dropdown.
So we use data validation in F3 to create a dropdown.
It doesn't need to be dropdown,
I just thought it looked a little bit nicer
and it has open or closed.
So let's select B3 through D14.
We'll come back up data validation.
And all we're doing is looking at the status of F3.
So we'll make another custom rule and we will say,
we'll start it with an equal sign as always.
Here we're going to lock both the column reference
and the row reference,
so that's two differences from where we started,
and we'll say, equal to open.
So if it passes this rule, then you can use the table.
If not, it won't work.
So if I change it from open to close, you already see
that it's giving you a data validation error in D3,
but that was already in there.
So let's try another one.
We'll just put a five in D5
and we get the data validation error.
So you aren't able to enter anything into this table
unless you go back up to the dropdown box
and you open it again.
There we go.
So before you apply this rule,
let's just make sure that it makes sense for your situation.
So an alternative to doing data validation
is let's just turn this off.
We'll say clear all.
Is that when you come in here,
you may just want to apply a filter.
So we'll left click on this filter.
Since this is formatted as a table,
I have the filters at the top and just only show shipped.
And then when you come in and show shipped,
then you can enter these tracking numbers
and the shipping service.
And you won't accidentally apply it to something else
because you can't see them.
And then when you're done,
you can just take the text filter off.
All right, so just choose
which one works best for you, right?
So here's a collection of other Excel videos.
If you liked this video,
there's a lot of other videos on this channel
that are really similar to this,
just a little tips and tricks, and I hope you enjoyed it.
Thanks for watching.