Drop-down list from cell formula (not data validation)

Question

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(...) ))

user0 · Accepted Answer · 2019-03-12 19:22:32Z

1

EXAMPLE 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:

answered Mar 12, 2019 at 19:22

user0

26k8 gold badges49 silver badges77 bronze badges

I cannot try the drop-downs in the demo sheet because it's view only.
– Ivan Dossev
Mar 12, 2019 at 20:30
1

make yourself a copy of that sheet
– user0
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.
– Ivan Dossev
Mar 12, 2019 at 20:33

Add a comment |