0

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:

  1. 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.

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

kbt
  • 13
  • 1
  • 6
  • Can you post a link to the sheet? – Chef1075 Aug 12 '18 at 15:00
  • https://docs.google.com/spreadsheets/d/1faTI9z8fADSAVldgfR9TzWPo0Z72FEXzVxkFb6KMF3g/edit?usp=sharing – kbt Aug 13 '18 at 02:59
  • Have you seen this question? https://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets – Max Makhrov Aug 13 '18 at 09:23
  • yes, the solution doesn't work here. – kbt Aug 13 '18 at 19:00
  • You need to make the spreadsheet public so we can look at it. Please review this post on how to ask a good question: https://stackoverflow.com/help/how-to-ask – Chef1075 Aug 13 '18 at 20:28
  • I'm really just asking how to take the below, but apply to successive cells down the column - Is there some way to iterate through the cells so that "A2" in the formula gets updated when you move to A3, without doing it manually. https://stackoverflow.com/questions/43890418/dynamic-dropdown-list-in-google-sheets-prefer-without-script – kbt Aug 14 '18 at 00:57

1 Answers1

0

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);
}
S Mullins
  • 1
  • 3