Multiple dependent drop-down lists in google sheets without script

Question

I work with G sheets in various contexts and this is a pretty frequent use case. In this case, let's say the spreadsheet looks like this: https://docs.google.com/spreadsheets/d/1PAg2Gr2T1gUmqlbAJaJjMQ37yO_YI5sYz9WMY3Q1lEg/edit?usp=sharing

Continents in Column B, Countries in Column C. And then, on another sheet, a list of values that correspond with a given country. So what I want is in Column C to select from the list of continents, but in column D, select from the list of countries within that continent.

Col. B | Col. C | Col. D |

Paris | Europe | France|

I've reviewed questions going over how to do this with scripts, but can't seem to replicate it. I'm also wondering if there's a quick-and-dirty way to do this with formulas or by adding additional filtered tables.

Answer 1

You can use a script or a formula to accomplish a dependent drop-down list.

Google Apps Script

Single dropdown menu:

Create a dropdown list in cell C6 of the Data sheet that takes values from the range B3:B in the Values sheet (column Region). Then copy the code below to the script editor and click on save. Every time you select a new item in the dropdown list in cell C6, a dependent dropdown list will appear/updated with a list of the corresponding countries in cell D6. See Instructions on how to setup and use this solution.

function onEdit(e){

    const as = e.source.getActiveSheet();
    const val = e.range.getValue();
    const val_not = e.range.getA1Notation();
    const vs = e.source.getSheetByName("Values");
    const opts = vs.getRange('B3:C' + vs.getLastRow()).getValues();
      
    if (val_not =='C6' && as.getName() == "Data"){
      
      const fopts = opts.filter(o=>o[0]==val)
      const droplist = fopts.map(o=>o[1]).sort().reverse().filter((v, i, a) => a.indexOf(v) === i);   
      const cell = as.getRange('D6');
      const rule = SpreadsheetApp.newDataValidation().requireValueInList(droplist).setAllowInvalid(false).build();
      cell.clearContent();
      cell.clearDataValidations();
      cell.setDataValidation(rule);            
    }      
  } 
  

Instructions:

---

Multiple dropdown menus:

The logic is exactly the same as before. The only modifications you have to do is to drag up/down the dropdown list in column C to the cells that you want to have a dropdown list and use this script instead:

function onEdit(e){

    const as = e.source.getActiveSheet();
    const val = e.range.getValue();  
    const row = e.range.getRow();
    const col = e.range.getColumn();
    
    const vs = e.source.getSheetByName("Values");
    const opts = vs.getRange('B3:C' + vs.getLastRow()).getValues();
      
    if (row > 2 && col == 3 && as.getName() == "Data"){
      
      const fopts = opts.filter(o=>o[0]==val)
      const droplist = fopts.map(o=>o[1]).sort().reverse().filter((v, i, a) => a.indexOf(v) === i);   
      const cell = as.getRange(row,4);
      const rule = SpreadsheetApp.newDataValidation().requireValueInList(droplist).setAllowInvalid(false).build();
      cell.clearContent();
      cell.clearDataValidations();
      cell.setDataValidation(rule);            
    }      
  } 

---

Google Sheets Formula

Instead of providing an explanation here which will make this post even longer than already is, I prefer to redirect you to a YouTube video that will help you figure out how to set up a dependent dropdown list using google sheet formulas:

Google Sheets - Drop Down List, 2 Dependent Dropdown Lists