Excel Dependent Data Validation Lists.


 This tutorial will walk you through the process of setting up dependent data validation lists. You could refer to this as cascading data validation.

I use this method as a sheet based alternative for variable data validation. This is a very useful tool when dealing with long lists. It means you can break the list into categories and only have that part of the list show in the data validation.

All you have to do is select a category in one cell and have the data validation dependent on that selection.

Here is an illustration of it working in one of the tutorials. Invoice Generator Project.

gif valid

In this article I will use the information from the roster project.

I receive a lot of emails and comments about this process indicating the many may find it a bit of a challenge. In the video below and the short article to follow I hope to make learning this process a little easier.


 

There are 3 things that we will look at.

1. How to add the formula to the data validation list source.

2. Breaking down the Offset dependent data validation formula to understand how it works.

3. Adapting the formula for Office 2007 and 2013

 

Adding the formula to the data validation list source

In this section we will look at adding the formula to the data validation.

Remember we are working with dependent data validation so first it is necessary to set up the cell that will hold the dependent list of categories.

Here are the steps

First set up a workbook with 2 sheets 1 called Data and the other called Lists.

On the Lists Sheet

Add your data to the list sheet as shown here.

Cascading Data Validation

On the Data Sheet

  1. Select cell E6 on the data sheet
  2. On the Ribbon choose Data / Data Validation
  3. In Settings select List from Allow
  4. Click in the source box and go to the list sheet and scroll over your list of categories.
  5. Click OK

 

Now for the dependent validation

  1. Select the area where you want to add the dependent validation.
  2. On the Ribbon choose Data / Data Validation
  3. In Settings select List from Allow
  4. Click in the source box and paste in the formula below.

 

=OFFSET(Lists!$F$6,MATCH($E$7,Lists!$E$7:$E$44,0),0,COUNTIF(Lists!$E$7:$E$44,$E$7),1)

Dependent Data Validation

 

You can now check to see if all is working OK.

 

How it works.

Ok. Let’s break this rather intimidating looking formula down top its basics.

We are using the Offset Function

Here is the Syntax for the Offset Function

OFFSET(reference,rows,cols,[Height],[Width])

Here is our formula.

=OFFSET(Lists!$F$6,MATCH($E$7,Lists!$E$7:$E$44,0),0,COUNTIF(Lists!$E$7:$E$44,$E$7),1)

 

  1. OFFSET                              (Lists!$F$6,
    [This is the starting cell to offset from. It is the header for our codes on the Lists sheet.]
  2. reference,rows             MATCH($E$7,Lists!$E$7:$E$44,0)
    [Now we are going to find the first match for the category selected in E7 on the Data Sheet in our range on the List Sheet. This will give us a number .If your category was Early then the number would be 1 if the Category was Late then the number would be 4.]
  3. cols                                     ,0,
    [We are not offsetting any columns at this stage]
  4. [Height]                          COUNTIF(Lists!$E$7:$E$44,$E$7)
    [We are counting how many rows to show by using the Countif Function]
  5. [Width]                             ,1)
    [All we need to do is offset one column]

 

Explanation

  1. We are offsetting from F6 on the Lists set. The starting row is determined by matching the value in E6 on the Data sheet with the List in column E. We find the first match and the number of rows down from row 6 is assigned. So now we have a starting point. No columns are offset at this stage.
  2. Next we need to find out how many items there are in this category so we use the Countif function to count the number based on the criteria in E6.
  3. We then offset 1 column to column F and we now have our range.

Watch the video above for a visual explanation.

 

Adapting the formula for Office 2003 and 2007

If you are using a version prior to 2010 we cannot refer to another sheet in a formula in Data Validation or in Conditional formatting.

To work around this we need to add 3 named ranges.

How do you add a named range?

First select the data to be referenced and in the name box at the top left of the sheet. Type in your name and hit the Enter key.

Here are the ranges and the names I have used.

Cascade      =Lists!$F$6

Category     =Lists!$E$7:$E$1000

Type             =Lists!$C$7:$C$10

 

This is the named range if you want the range to be dynamic.

Category     =OFFSET(Lists!$E$7,,,COUNTA(Lists!$E$7:$E$1000))

On the Data Sheet

1. Select cell E6 on the data sheet
2. On the Ribbon choose Data / Data Validation
3. In Settings select List from Allow
4. Click in the source box and hit the F3 key and select the named range Type
5. Click OK

Now for the dependent validation

1. Select the area where you want to add the dependent validation.
2. On the Ribbon choose Data / Data Validation
3. In Settings select List from Allow
4. Click in the source box and paste in the formula below.

=OFFSET(Cascade,MATCH($E$7,Category,0),0,COUNTIF(Category,$E$7),1)

Conclusion

I am sure you will find this a very useful tool to have in developing your applications. I have used this in many of the projects as a very effective way to deal with large lists.

The data entry process will be easier and quicker and accuracy will be quaranteed.