Sep 05, 2016

You can get Excel not to accept a cell value if it falls outside a criterion. You specify the criterion in Data Validation located in the Data tab. The criterion, however, can sometimes be dynamic in the way that it might depend on the value of another cell or cells. In these situations, you need to use a Custom Criteria. Here’s an example:

Let’s say we want to allow input in a cell only if the value of another cell is either Option1 or Option3, but not Option2.

So we click on C6 in the picture and apply a Data Validation:

Data tab > Data Validation

And set values as shown in the picture below:

That’s all you need. If the user selects Option2 in C2, then Excel does not allow the user to add any value in C6, because any value there will be invalidated.

You can make the user see that the cell C6 is not available for Option 2 by changing its cell colour to grey using Conditional Formatting like so:

Click on C6 and go, Home > Conditional Formatting > New Rule

Select Use a formula to determine which cells to format and enter this formula;

=C2=”Option2”

And then click on Format and set the Fill colour to grey and click on OK and OK.

Now every time C6 is set to Option2, C2 will turn grey indicating that it is not available.

For more information, take a look at New Horizons' Microsoft Excel training courses.

Cyrus Mohseni