Creating a dynamic dropdown based on multiple criteria in Google Sheets

Question

I have a sheet with two tabs. One one tab I have a master list of values. On the second sheet, columns A, B and C have fixed dropdown validation. How can I set up my second sheet so that the data validation in column D is dynamic based on Columns A, B and C?

I have tried writing an app script to look at the values of column A, B and C to create an array of appropriate values and then dynamically create the validation but haven't been able to come up with a way that works on a row by row basis.

Has anyone made this work before or can think of a way to do so?

I often create Named Ranges that are fairly large and build the validation with the Named Range and I can populate the values programmatically because the validation doesn’t take blank cells — Cooper

Umair Mohammad Umair Mohammad · Accepted Answer · 2019-05-14T08:34:35

You can use DataValidators.

// Set the data validation for cell A1 to require "Yes" or "No", with dropdown menu.
var cell = SpreadsheetApp.getActive().getRange('A1');

var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], true);

cell.setDataValidation(rule.build());

Credit & Reference : https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder#requireValueInList(String,Boolean)

Since you want to dynamically update the list, you can fetch data from that another sheet and then update the data validation with new data.

Creating a dynamic dropdown based on multiple criteria in Google Sheets

1 Answers