Data Validation List Based On Criteria

Excel With Excel Logo

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.


What do I mean by this?

If we use an example where I have 2 columns:

  1. Column A will have a simple Data Validation List rule that will allow the user to select a country from column D
  2. 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.

Example of set up for data validation list based on criteria

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.

image showing data validation list rule

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?

Click Here to Leave a Comment Below 0 comments

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: