Data Validation - Cascading Lists (Using INDEX & OFFSET function)

Hello. We already saw 3 ways of getting cascading lists in your workbook using IF function, INDIRECT function & CHOOSE function. Let’s also see one more way of doing this using combination of INDEX and OFFSET functions. This is one of my preferred way, reason being you end up using very few Named Ranges. One more benefit is you can keep on adding new items in the list without worrying about modifying formula’s or anything. Let’s dive deep and find out how.

For a quick recap of our example, we have country, region and state to be selected from the drop down lists and we wanted these lists to be cascading / dependent on previous option selected.

For a change, I kept my database in a different sheet. Let’s name this sheet as Data Base. Also name the sheet where you are applying validation as Data Entry. Going forward, we are going to use these names frequently so remember them.

Arrange Your Data

To get this method to work, you will have to arrange your data in a particular way. Look at below picture on how I arranged it.

In column A of Data Base sheet, enter items you want in first drop-down list with a heading in cell A1. In our example, we have 2 countries to be given.

Moving to the right of column A, make one column for each item of the first drop down list with item name in first cell of the column. In our example, I entered India in cell B1 and USA in cell C1.

In each column, enter the drop-down list items relating to that heading. In above example, I enter South India, North and so on under India. Enter the items for USA as well.

Moving to the right, for each item in the second drop-down, create a different column by following the logic in previous paragraph. Once you are done arranging your data, your Data Base sheet would look like as in above picture.

Define Named Ranges

As mentioned above, we will be using very few Named Ranges. In fact, we are going to use only 3 of them. Go to your Data Entry sheet and do the following.

Named Range 1:

Click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name FirstList. In the Refers to: field, enter the below formula

=OFFSET('Data Base'!$A$2,,,COUNTA('Data Entry'!$A:$A)-1)

Click OK. This formula creates the first drop-down lists with countries in our example. This formula is dynamic, that means it's size increases as you type new entries.

Named Range 2

Click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name DataRange. In the Refers to: field, enter the below formula

=OFFSET('Data Base'!$A$2,,,100,COUNTA('Data Entry'!$1:$1))

Click OK. This formula creates an array of cells with possible drop-down options which we will use in the next Named Range. This is also dynamic range.

Named Range 3

Select cell B2 and click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name DropDownList. In the Refers to: field, enter the below formula. (This formula is long; it should be in one line)

=OFFSET(INDEX(DataRange,1,MATCH('Data Entry'!B1,'Data Base'!$1:$1,0)),,,COUNTA(INDEX(DataRange,,MATCH('Data Entry'!B1,'Data Base'!$1:$1,0))))

If you notice, this formula uses Relative References. Selecting cell B2 before entering this formula is absolutely necessary for this formula to work as expected.

This formula checks for the option selected in cell B1 (first drop-down) and finds exact value in Data Base sheet and returns all the items under that column.

Once you are done naming as above, your Name Manager should look as in picture below.

Apply Validation

Last step is to apply validation.

Select cell B1 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =FirstList

Select cell B2 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =DropDownList

Select cell B3 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =DropDownList

That’s all. Check the drop-down lists and its list items.

You will just have to expand the Data Base sheet if you have additional entries in the future. For example, if you got one more country to be added, go to Data Base sheet and add the new country name in cell A4. In last column to the right, enter the new country name in first row along with options under it in below cells. Follow the logic in Arrange Your Data section above. That’s all you have to do. Validation lists will be updated automatically. Once you are done with arranging, go to validation lists and check it.

You can download the example file I used by clicking here. Post a comment if you have any doubts relating to this post.

I am concluding Data Validation series with this post. I will only make another post on this feature when I feel there is some useful information I should add. Meanwhile, let me know if you want me to cover any particular scenario. Happy Learning!!