Using cell contents to modify drop-down menu list

Question

I have a Google Sheets where I am saving information about universities and I have a column for regions and countries (regions or continents, however you want to see it) and what I'd like to do is have the regions column contain a drop-down menu (data validation) of all the continents and depending on what continent you select, the countries column will have the corresponding countries.

I have another sheet containing the required data with the first row being the continent names and the remaining rows being the countries in that continent.

I've tried custom formulas but ended up having something too complex that didn't work so I deleted it.

If anyone knows how I could do this with either the script editor or directly in data validation, that'd be great!

Answer 1

Continent and Country Dropdowns

I apologize but this is a little involved. Let's start with where I got the data. This is the website: https://datahub.io/JohnSnowLabs/country-and-continent-codes-list

I actually just copied and paste it into a Sheet named "C&C". I sorted it by continents and then by countries and I depend upon it remaining like that since it reduces the amount of effort to get the countries.

And here is the Google Script Code:

function onEdit(e) {
  var dlm=" - ";
  var msg="Start" + dlm;
  var sh=e.range.getSheet();
  if(sh.getName()!='DropDown')return;
  if(e.range.getA1Notation()=='A2' && e.value) {
    var dsh=e.source.getSheetByName('C&C');
    var drg=dsh.getRange(1,1,dsh.getLastRow(),1);
    var dvA=drg.getValues();
    var cA=[];
    var cStart=0;
    var cEnd=0;
    for(var i=0;i<dvA.length;i++) {
      if(!cEnd && !cStart && dvA[i][0]==e.value) {
        cStart=i+1;
        msg+="cStart: " + cStart + dlm;
        //e.source.toast(msg);
      }
      if(!cEnd && cStart && dvA[i][0]!=e.value || i==dvA.length-1) {
        cEnd=i+1;
        msg+="cEnd: " + cEnd + dlm;
        //e.source.toast(msg);
        break;
      }
    }
    var crg=dsh.getRange(cStart,3,cEnd-cStart+1,1);
    var cvA=crg.getValues();
    e.source.getRangeByName('Countries').clearContent();
    sh.getRange('B2').clearContent();
    e.source.getSheetByName('NamedRanges').getRange(2,2,cvA.length,1).setValues(cvA);
  }
  msg+="Exit" + dlm;
  e.source.toast(msg);
}

I left the debug toasts in there in case you want them.

Here's what the C&C page looks like:

And here's the page where I placed the NamedRanges. I named that page NamedRanges:

And here's the page with the two dropdowns:

It's displaying the South America continent selection.

Here's the NamedRanges setups:

And here's the Data Validation Setups: