CODEWITHSUNDEEP
if-statementgoogle-sheetsdrop-down-menugoogle-sheets-formulaarray-formulas
The_Train
The_Train

Reputation: 331

Multi row dependent dropdown

I'm currently working on adding a dependent drop down to a spreadsheet I'm working on. I've used this formula to create a working dependent drop down on one row...

=if(J9=Operators!B1,indirect("Operator"),if(J9=Operators!C1,indirect("Livery")))

....but I wish for this to be replicated in each row down to J65. I've played around with my formula to no avail and have also made use of an array/transpose formula which I found via a google search but this only creates an error message asking me to add 700 more columns

Upvotes: 2

Views: 935

Answers (1)

player0
player0

Reputation: 1

you will need arrayformula like this:

=ARRAYFORMULA(IFERROR(IF(J9:J65="Livery", 
 TRANSPOSE(FILTER(Operators!B2:B, Operators!B2:B<>"")), IF(J9:J65="Operator", 
 TRANSPOSE(FILTER(Operators!C2:C, Operators!C2:C<>"")), ))))

which will generate the items for dropdown and then you need to create a dropdown per each cell in L column

0

0

spreadsheet demo for first 3 dropdowns in L column

Upvotes: 2

Related Questions