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