0

I am trying to create a tracker that has two dropdown columns that are dependent on the same column. For example, I am providing 3 different services to clients, and each service has it's own type of support and outcome. When one of the services is selected I would like the support and outcome columns to populate with the items that are relevant to that service.

Currently some of the outcome options will populate, but not all of them.

I found and am trying to manipulate code used in this post, How do you do dynamic / dependent drop downs in Google Sheets?.

I created a second section to get the dropdowns for the outcomes. Here's a sample document of what I've been able to accomplish, https://docs.google.com/spreadsheets/d/1KmET4ilVqxGQwnIKGGo2hPFxWcQCtrXta-z8OHJT_5c/edit?usp=sharing

Thank you!

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var myRange = SpreadsheetApp.getActiveRange();
  var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dropdowns");
  var option = new Array();
  var startCol = 0;

   // Dynamic dropown for Support Provided
  if(sheet.getName() == "Tracker" && myRange.getColumn() == 3 && myRange.getRow() > 1){  
    if(myRange.getValue() == "Credit"){
      startCol = 3;
    } else if(myRange.getValue() == "Legal"){
      startCol = 4;
    } else if(myRange.getValue() == "Housing"){
      startCol = 5;
    } else {
      startCol = 6
    }

  if(startCol > 0 && startCol < 6){
    option = dvSheet.getSheetValues(3,startCol,6,1);
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);  
    //dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
   }

  if(startCol == 6){
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
  } 
  } 

     // Dynamic dropdown for Outcome
  if(sheet.getName() == "Tracker" && myRange.getColumn() == 3 && myRange.getRow() > 1){  
    if(myRange.getValue() == "Credit"){
      startCol = 7;
    } else if(myRange.getValue() == "Legal"){
      startCol = 8;
    } else if(myRange.getValue() == "Housing"){
      startCol = 9;
    } else {
      startCol = 10
    }

  if(startCol > 6 && startCol < 10){
    option = dvSheet.getSheetValues(6,startCol,10,1);
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);  
    //dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 2).setDataValidation(dv.build());
   }

  if(startCol == 10){
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 2).clearDataValidations();
  } 
  } 


}
Christina McClain
  • 3
  • 1

1 Answers1

0

You have specified incorrect start row (i.e. "6") for the option list of the Outcome dropdown (code line 48):

option = dvSheet.getSheetValues(6,startCol,10,1);

Replace "6" by "3" and you will have correct options list in the dropdown.

Александр Ермолин
  • 1,145
  • 1
  • 10
  • 17