Resume: Hello. I made a dependent drop down option in Google Sheet. But I need to set validation data sequentially for all rows in the sheet. I don't want to do it manually.
Deitals: I have a Category and sub-category Drop Down menu
This items are filled with data from sheet which I made some named ranges and this formulas fill the subcategory dropdown:
=IF(A1<>"";TRUE;FALSE)
=IF($D1;TRANSPOSE(INDIRECT(QUERY(Categories!$H$2:$I$1000; "SELECT I WHERE H = '"&A1&"'")));TRANSPOSE(INDIRECT("all_subcategories")))
The query inside the first INDIRECT will return one of this values:
First verify if Category was chosen and case yes query for named range name for chosen category and use it with INDIRECT function to get list range for dropdown. If category is empty I use another named range with all subcategories.
But How to set this data validation for all rows in this sheet????
To set data validation in this window I need to choose a range to apply data validation and criteria which I chosen interval list. If I set a range like A2:A1000 all lines will have validation data based on same transpose line.
OBS: Custom Formula does not worked for me.
I made an example sheet to share with you
