Dynamic & extendible category names

Question

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.'

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.