Conditional Data from a Range in Another Sheet

Question

I have IF(D2="PLUMBER","x","") in cell E2, for example.

I need x to refer to a range of cells in another sheet so that if PLUMBER is selected, E2 will show a drop-down menu (Data Validation option in Google Spreadsheet).

Also, I need to incorporate other possibilities too. For example, D2 could be REAL ESTATE AGENTS and then I would need a list of agents in E2 upon it's selection. In other words, D2 will be a drop-down menu as well.

wchiquito wchiquito · Accepted Answer · 2013-10-10T09:40:10

With the recent addition of data validation in Apps Script, I see possible what you need to do.

The following is a small sample code that can be useful to implement your own solution tailored to your requirement.

/* CODE FOR DEMONSTRATION PURPOSES */
function onEdit(e) {
  var ss, cell, range, rule;
  if (e.range.getColumn() === 4) {
    ss = SpreadsheetApp; 
    cell = ss.getActiveSheet().getRange(e.range.getRow(), 5);
    if (e.value === 'PLUMBER') {
      range = ss.getActive().getRange('A2:A6');
      rule = ss.newDataValidation().requireValueInRange(range).build();
      cell.setDataValidation(rule);    
    } else cell.clearDataValidations();
  }
}

Conditional Data from a Range in Another Sheet

1 Answers