how to create cascading dropdowns in google sheets
IN SUMMARY
Creating cascading dropdowns in Google Sheets allows you to create dependent dropdown lists, where the options in one dropdown depend on the selection in another. This can be achieved without using any code, by leveraging the built-in data validation and filter functions.
Setting up the Data Source
Create a table with columns representing the different levels of your cascading dropdowns. For example, if you have categories, subcategories, and items, you would have three columns for each level.
Create a new sheet (e.g., 'Conditional Dropdowns') where you will set up the data source for your cascading dropdowns. Copy the header row and the data from your original table to this new sheet.
In the 'Conditional Dropdowns' sheet, create a formula that uses the FILTER function to retrieve the relevant options based on the previous selection, and the TRANSPOSE function to display the options horizontally. For example: `=TRANSPOSE(UNIQUE(FILTER($B$2:$B$100, $A$2:$A$100=A2)))` will filter the options in column B based on the value in cell A2.
Creating the Cascading Dropdowns
In your input sheet, create a cell for the first dropdown. Right-click on the cell, select 'Data Validation,' and choose 'Criteria' as 'List from a range.' Select the range containing the first level of options from your data table.
Create a cell for the second dropdown. Right-click, select 'Data Validation,' and choose 'Criteria' as 'List from a range.' This time, select the range containing the filtered options from the 'Conditional Dropdowns' sheet, corresponding to the first level of options.
For each additional level of cascading dropdowns, create a new cell and set up the data validation rule to reference the filtered options from the 'Conditional Dropdowns' sheet, based on the previous selection.
Considerations and Limitations
If you need to insert new rows in your data table frequently, this method may become cumbersome. In such cases, it might be better to use a script or a third-party add-on to manage the cascading dropdowns.
For large datasets, the performance of the cascading dropdowns may be affected, as the filtering and transposing operations can become computationally intensive.
If you need to make changes to the data source or the structure of the cascading dropdowns, you will need to update the formulas and data validation rules accordingly, which can be time-consuming.