Google-sheets – Data Validation with an IF formula

data validationformulasgoogle sheetsgoogle-sheets-queryworksheet-function

I've got a list of items that I want to include in a Drop Down. Using data validation, all I need to do is add the range and this works. However, the list has various types of data and I want to create a Data Validation that gives options from the given range if the type is correct. Is this possible?

I've added a link to the list. Essentially, let's say for Life providers, I want to have data validation showing all items in the first column IF their type is Life.

enter image description here

Best Answer

yes that is possible. check this example:

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);))