Google sheets multiple dependant lists dynamically

user_1387940 0
Viewed 81

I'm collecting data in a sheet from people so they can categorise the product types. I have a sheet with thousands of products in rows. For each product i want to know the Type and sub-type for each product.

For e.g. If type is Apparel then the sub-type (dependant dropdown) shows data based on Apparel options.

Apparel options

If type is Accessories then the sub-type shows relevant data.

Accessories options

What i'm able to do is when i select a type, the sub-type column data changes based on that, but i want to make it a drop-down list so a relevant sub-type can be selected for that row. Subtype

Would really love and appreciate the help.

Thanks

google-sheets-formula google-sheets drop-down-menu
1 Answers

One way to do this is with named ranges and INDIRECT function.

Based on the example below. enter image description here

First you will have to name the ranges, go to 'Data' -> 'Named Ranges', select the cells that have the apparels, name that range 'Apparel' and do the same with the other lists.

Create the first drop-down that lists Apparel/Accessories,etc. as the options. In this case the range I used is =A1:B1.

Before you create the other drop-down, go to an empty cell or create another sheet and enter the following formula =INDIRECT(D1).

Now you can insert the second drop-down next to the first one. In the data validation criteria specify the cells that contain the result from the INDIRECT function.

user_179264780
Related