Data Validation List Based On Criteria
In earlier posts, we covered data validation list, from basic to more complex rules. In this post, we will create a rule for a Data Validation List that will be dependent on another value.
If you want to read back over earlier posts, you can click on the links below.
- Introduction to Data Validation Lists
- Data Validation Lists Based On A Range Of Cells
- Data Validation Lists To Automatically Update Source Range
What do I mean by this?
If we use an example where I have 2 columns:
- Column A will have a simple Data Validation List rule that will allow the user to select a country from column D
- Column B will be the focus of this post. The rule for the Data Validation List will be dependent on what country is selected in column A.
If the country in column A is Ireland, then the list in column B will only list the locations from column E (Ireland).
You will only see the list options that you need to see, you won’t have to scroll through all locations for all countries (as you would have to if a simple rule was set up based on a list of locations).
An Intermediate to Advanced level of Excel knowledge may be needed here if you want to recreate this rule yourself. For the sake of demonstration I have put the sources to all my Data Validation List Rules on the same sheet as the cells that I will be applying the rules too. Normally i would not do this, I would have them on a different sheet.
How do we do this?
The source for our list will be based on an IF statement.
For cell B2, we are essentially going to say:
- If A2 is Ireland, then the source will be from column E
- Or, If A2 is Wales, then the source will be from column F
- Or, If A2 is Scotland, then the source will be from column G
- Or, If A2 is England, then the source will be from column H
- Or Else (i.e. none of the above countries are selected in A2) the only option available is “Select a country first”
The full IF function will be: =IF(A2=”Ireland”,$E$2:$E$13,IF(A2=”Wales”,$F$2:$F$9,IF(A2=”Scotland”,$G$2:$G$10,IF(A2=”England”,$H$2:$H$16,$D$1))))
To understand and read the function note the commas, the use of $ and brackets (each opening bracket has a corresponding closing bracket).
If building your own rule like this, I suggest building it piece by piece. In my case above, I created the first IF function, if A2 is “Ireland” then E2:E13 or else D1.
When I was happy with that, I amended the value_if_false to be another IF function, this time checking if the country is Wales and so on.
The first time I was asked for a Data Validation List rule like this it was for a situation where the drop-down list had to be changed based on a numeric value. If the number was below the target then the list should give main reasons why the target wasn’t reached, or else it was to be blank because a comment wasn’t required.
Could you replicate a rule like this?
