# Excel Dependent Data Validation Lists.

Contents

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.

### 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.

## Watch this video for a full explanation on dependent data validation.

## Template for the project

Many of the projects on Online PC learning use this process to make filling in data easy and effective. You will be able to download the template by going to the website above.

*Download the practice file.*

## 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.

### On the Data Sheet

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

### Now for the dependent validation

- Select the area where you want to add the dependent validation.
- On the Ribbon choose Data / Data Validation
- In Settings select List from Allow
- 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)

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)**

**OFFSET**(Lists!$F$6,

[This is the starting cell to offset from. It is the header for our codes on the Lists sheet.]**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.]**cols**,0,

[We are not offsetting any columns at this stage]**[Height]**COUNTIF(Lists!$E$7:$E$44,$E$7)

[We are counting how many rows to show by using the Countif Function]**[Width]**,1)

[All we need to do is offset one column]

### Explanation

- 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.
- 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.
- 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.

It's not very clear when you select the product from check box and it will automatically populate additional cells in his order & inventory form. Need more help.

Great Day !

Sir Can you give some Example, about this Situation..

Situation:

.. 3 Cascading Data Validation, Example. "CATEGORY, DESCRIPTION and MODEL"

Category, Description, and Model Link Together,

Example:

.. When you Choose 1 to CATEGORY, You can see to the DESCRIPTION all of the DESCRIPTIONS you Choose in the CATEGORY, and if you Click to Model, You Can See the MODELS to the DESCRIPTION you Choose. . .

I So Very Depress Regarding that T_T

I Try Many Formula But Now Working

Hope You Help Me .

Thank you and God Bless….

Hi Elvis,

This tutorial is for 2 two lists. Are you wanting 3 three?

Best wishes

Trevor

Oopps. Sorry Sir ..

.. Yes Sir Trevor Easton, i Dont Know how to Work with 3 three Field,

CATEGORY, DESCRIPTION and MODEL, the Model Cascading to Description.

.. Like i Said My Brain so very Pain About that 1 Field "MODEL" . huhuhu

Thank thanks thanks….

Thank you and

God Bless…

Will This work in a file that is being shared? Excel 2013