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

formulasgoogle sheetsgoogle-sheets-data-validation

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

Best Answer

EXAMPLE 1:

Sheet1:

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

Sheet2:

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

EXAMPLE 2:

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

Related Solutions

Google-sheets – Filter based on Data Validation drop down list

Okay so I solved this problem with this function:

=sum(filter(J6:J25,K6:K25="Meals",month(B6:B27)=7))

This would give me the subtotal for all Meal expenses for the month of July. This is a bit of a pain to apply when referencing this data in other sheets, but it works.

Google-sheets – Is it possible to have a Data validation with a Custom formula WITH a dropdown list

This can be done using List from a range criterion.

On another sheet, say Sheet2, enter conditional formulas that will be either blank or a number, based on B7 cell. Like this, entered in cells A1:A26 of Sheet2:

=if(Sheet1!$B$7="Yes",,row()-row(A$1)+75)

This will be either blank or the numbers 75..100.

In B8 of Sheet1, use data validation "List from a range": Sheet2!A1:A26 . This will give you in-cell dropdown, which will be either empty or contain numbers 75..100, depending on what's in B7 of Sheet1.