Hello All. In this tutorial, let’s explore how to create drop down lists (data validation lists) based on a particular value selected from another list. Let’s look at an example below:
Here, let’s create two lists. The first one will have the main category (State / Animal / Games / color). Based on the value selected in the first list, the second list would display the appropriate items as shown below
First let’s create the first list. A simple data validation selecting B2:B5 (or use a Name manager to create the list) as the data. Navigate to Data tab -> Select Data validation under the Data Tools section.
Now, let’s create a Name Range for the sub categories. This is important step without which the second list won’t work. To easily create a name range, select the items in the list give a name in the Name box. In our example, let’s select C2:C6 and provide the name State. Please note the name should be same as one of the item in the main category. More on name range here.
To verify, let’s check the list of name managers created. Navigate to Formulas -> Name manager under Defined names section. You should be able to see a name for all the sub categories
Now, let’s do the data validation for the second list. Navigate to Data -> Data validation under data tools. Source of the list would be sub category of the value selected in the first list. For this, we use indirect function.
Here, D9 is the first list. Indirect function would help in returning a valid reference from a given text string. Here, the text string is a valid name in the name manager and hence it would return the reference of the same. More on indirect function here.
Now we have created the second list, that would display values based on the value selected. You can nest more and more lists based on the need with the same logic.
I hope you liked this short tutorial that comes handy in many situations. Please let us know your valuable feedback that would help to improve the content. Thanks for reading.