google sheet dynamic drop-down dependent list

Question

I was able to create a dynamic drop down list in google sheets where for example: If a user selects something in Column A (Make of car: Toyota); Column B Auto populates with the models for that make (Models: Prius, Highlander etc).

I am running into two issues:

I need these drop-downs to work down the successive rows. I've copy and pasted and adjusted the formulas, but the dynamic quality no longer works when I paste it down to successive rows.
If the value in Column A is Toyota, and Column B populates with Prius. When I change column A to Mercedes for example, Column B keeps "Prius" and has an error "Invalid Input - Input must fall within specified range), however if I click on Column B, I'll get the appropriate drop down for mercedes models. I need to have the field clear on click of Column A and repopulate with the dynamic drop down.

This is the formula I am using: =TRANSPOSE(INDIRECT("D" & MATCH('Content Tracker - GENERAL RESOURCES'!D2, C1:C, 0) & ":K" & MATCH('Content Tracker - GENERAL RESOURCES'!D2, C1:C, 0)))

Have you seen this question? stackoverflow.com/questions/21744547/… — Max Makhrov
You need to make the spreadsheet public so we can look at it. Please review this post on how to ask a good question: stackoverflow.com/help/how-to-ask — Chef1075

S Mullins S Mullins · Accepted Answer · 2020-05-27T05:09:49

The best way to do this and possibly the only way requires a little coding in the script editor.

I did a 3 level validation with solved this issue:

I defined the variables before this code.

function onEdit(event) {
  var activeCell = event.range;
  var val = activeCell.getValue();
  var row = activeCell.getRow();
  var col = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName()
  if (wsName === mainWsName && col === headerProv && row > 1) {
    validationProv(val, row);
  } else if(wsName === mainWsName && col === headerCanton && row > 1) {
    validationCanton(val, row);
  } 
} //end onEdit

function validationProv(val, row) {
  if(val === "") {
    ws.getRange(row, headerCanton).clearContent();
    ws.getRange(row, headerCanton).clearDataValidations();
    ws.getRange(row, headerDistrito).clearContent();
    ws.getRange(row, headerDistrito).clearDataValidations();
  } else {
    ws.getRange(row, headerCanton).clearContent();
     ws.getRange(row, headerCanton).clearDataValidations();
    ws.getRange(row, headerDistrito).clearContent();
    ws.getRange(row, headerDistrito).clearDataValidations();
    var filtroProv = options.filter(function(o){ return o[2] === val });
    var filtroCant = filtroProv.map(function(o){ return o[3] });
    var cell = ws.getRange(row, headerCanton);
    applyValidationToCell(filtroCant,cell);
  }
} //end validationProv

function validationCanton(val, row) {
  if(val === "") {
    ws.getRange(row, headerDistrito).clearContent();
    ws.getRange(row, headerDistrito).clearDataValidations();
  } else {
    ws.getRange(row, headerDistrito).clearContent();
    var headerProvVal = ws.getRange(row,headerProv).getValue();
    var filtroProv = options.filter(function(o){ return o[2] === headerProvVal && o[3] === val });
    var filtroCant = filtroProv.map(function(o){ return o[4] });
    var cell = ws.getRange(row, headerDistrito);
    applyValidationToCell(filtroCant,cell);
  }
} //end validationCanton


function applyValidationToCell(list,cell) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
  cell.setDataValidation(rule);
}

google sheet dynamic drop-down dependent list

1 Answers