I am trying to make a triplet dependent drop-list. The first is a typical drop down but the next two are dynamic.

Since you cannot enter a formula into the data validation criteria, how can you create a drop-down box in the cell formula?

The examples I find often use "array formula"

=arrayformula(IF(A1="x", INDIRECT("NamedRangeX"), IF(...) ))

but using this function will not turn the cell into a drop-down list, it will fill in the cells below which is the wrong functionality.

What I need is something like this and to have it work with NamedRanges:

=droplist(IF(A1="x", INDIRECT("NamedRangeX"), IF(...) ))

enter image description here

1 Answer 1




D20: Data Validation > List of items: I made a choice,I didn't make a choice

F20: Data Validation > List from a range: Sheet2!D8:D11


D8: =IF(Sheet1!D20 = B6; QUERY(B8:B11;;-1);IF(Sheet1!D20 = C6; QUERY(C8:C11;;-1);))


formula in D2:

=IF(A2=F2, {H3:H},
 IF(A2=F3, {I3:I},
 IF(A2=F4, {J3:J}, )))

data validation in A2:

data validation in B2:

demo spreadsheet

  • I cannot try the drop-downs in the demo sheet because it's view only. Commented Mar 12, 2019 at 20:30
  • 1
    make yourself a copy of that sheet
    – user0
    Commented Mar 12, 2019 at 20:32
  • Also this works well if you only need a few dynamic drop-downs, but I need to do this easily for hundreds of rows. It will be too bulky if you need a data-validation staging area for each instance of dynamic drop menus. Commented Mar 12, 2019 at 20:33

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.