I'm having trouble on something with Google Sheets: I need category names to be dynamic & extendible between 15-200 values, so it's not quite worth an Apps Script, would be super cool to put the following formula

=QUERY(A1:C4, "SELECT COL WHERE" &A1:C1& = &E8&)

in such a place as it could reference the dynamically selected dropdown in Cell E8, and return all the values in a dropdown in F8. I've seen videos of dependent dropdowns and 'helper columns' but I think this can be done with query, and putting that formula INSIDE the data validation formula field on that cell. Thoughts? I think the formula is close - it should use E8 to 'select that column, then return all values below it.'

enter image description here


1 Answer 1


putting that formula INSIDE the data validation formula field on that cell

That cannot be done with a spreadsheet formula, because currently (January 2023) Google Sheets data validation only supports one type of formula: a reference to a spreadsheet range, using any mix of absolute and relative row and column references.

What you can do is wrap the query() formula in transpose() and put it in a cell on the same row as the top-level drop-down. The second-level drop-down can then use the List from a range option to refer to the cells where the query() results are.

In practice, it is usually easier to use an onEdit(e) script to manage drop-downs. They let you easily implement multiple levels of dependent drop-downs. Take a look at the dependentDropDownLists_ script for one example.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.