Code.gs:

function onOpen() {

var ui=SpreadsheetApp.getUi();

ui.createMenu('My Tools')

.addItem('Run As Dialog', 'runAsDialog')

.addSubMenu(ui.createMenu('Utility')

.addItem('Create Named Range', 'jjeSUS1.createNamedRange')

.addItem('Display Active Range Formats','jjeSUS1.displayActiveRangeFormats')

.addItem('Select Skip Header', 'jjeSUS1.selectColumnsSkipHeader')

.addItem('Column Number', 'jjeSUS1.displayColumnNumber')

.addItem('Remove All Named Ranges', 'deleteAllNamedRanges'))

.addToUi();

}

function searchInit() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var drg=ss.getRangeByName('Departments');

var rule=SpreadsheetApp.newDataValidation().requireValueInRange(drg).build();

var h=getColumnHeight(3,sh,ss);

var rg=sh.getRange(h+1,3);

rg.setDataValidation(rule);

}

function getDropDown(pObj) {

Logger.log(JSON.stringify(pObj));

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var prg=ss.getRangeByName(pObj.value);

if(!prg){

//SpreadsheetApp.getUi().alert(Utilities.formatString('No additional data for current selection: %s', pObj.value));

ss.toast(Utilities.formatString('No additional data for current selection: %s', pObj.value), 'Status', 5);

return;

}

var rule=SpreadsheetApp.newDataValidation().requireValueInRange(prg).build();

var h=getColumnHeight(pObj.col,sh,ss);

var rg=sh.getRange(pObj.row,pObj.col);

rg.setDataValidation(rule);

}

function getDropDown2(o) {

//e.source.toast('gDD2 Entry');

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var nrsh=ss.getSheetByName('NamedRanges')

var parent=sh.getRange(o.rowStart,o.columnStart-1).getValue();

if(parent) {

o['mode']='value';

o['modeValue']=o.value;

o['nextCol']=o.columnStart+1;

var prntrg=ss.getRangeByName(parent);

Logger.log('parent: %s',parent);

var prntvA=prntrg.getValues().map(function(r){return r[0];});

var idx=prntvA.indexOf(o.value);

if(idx>-1) {

var nrvA=nrsh.getRange(prntrg.getRow(),prntrg.getColumn(),prntvA.length,4).getValues();

Logger.log('idx: %s, nrvA.length: %s,prntrg.getRow(): %s, prntrg.getColumn(): %s',idx,nrvA.length,prntrg.getRow(),prntrg.getColumn());

if(nrvA[idx][1]) {

o['mode']=nrvA[idx][1];

o['modeValue']=nrvA[idx][2];

o['nextCol']=nrvA[idx][3];

}

}

}

Logger.log('o.mode: %s, o.modeValue: %s, o.nextCol: %s',o.mode,o.modeValue,o.nextCol);

switch (o.mode) {

case ddEnum.match:

var prg=ss.getRangeByName(o.modeValue);

if(!prg){

ss.toast(Utilities.formatString('No match for: %s, mode: %s', o.modeValue,o.mode), 'Status', 5);

return;

}

break;

case ddEnum.term:

var prg=ss.getRangeByName(o.modeValue);

if(!prg){

ss.toast(Utilities.formatString('No match for: %s, mode: %s', o.modeValue,o.mode), 'Status', 5);

return;

}

break;

case ddEnum.value:

default:

var prg=ss.getRangeByName(o.modeValue);

if(!prg){

ss.toast(Utilities.formatString('No match for: %s, mode: %s', o.modeValue,o.mode), 'Status', 5);

return;

}

}

var rule=SpreadsheetApp.newDataValidation().requireValueInRange(prg).build();

var h=getColumnHeight(o.nextCol,sh,ss);

var rg=sh.getRange(o.rowStart,o.nextCol);

rg.setDataValidation(rule);

}

function clearRange() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var rg=sh.getRange(sh.getActiveRange().getRow(),3,1,sh.getLastColumn()-2);

rg.clearContent();

rg.setDataValidation(null);

rg.deleteCells(SpreadsheetApp.Dimension.ROWS);

}

function launchClearDialog() {

var html='<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';

html+='<style>input{margin:3px 2px 3px 0;}#clrrng{font-size:8px;width:120px;}</style>';

html+='<input type="text" placeholder="Select Row on the Spreadsheet" id="clrrng" /><label for="clearrang">Range To Clear</label><br />';

html+='<input type="button" value="Select Row" onClick="getSelectedRange();" /><br />';

html+='<input type="button" value="Clear Selected Row" onClick="clearSelectedRange();" /><br />';

html+='<input type="button" value="Exit" onClick="google.script.host.close();" /><br />';

html+='<script>';

html+='function clearSelectedRange(){var rg=$("#clrrng").val();google.script.run.withSuccessHandler(function(){google.script.host.close();}).clearRange(rg);}';

html+='function getSelectedRange(){google.script.run.withSuccessHandler(function(rs){$("#clrrng").val(rs);}).getSelectedRange();}';

html+='</script>';

var ui=HtmlService.createHtmlOutput(html).setWidth(275).setHeight(150);

SpreadsheetApp.getUi().showModelessDialog(ui, "Clear Selected Row");

}

function getSelectedRange() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('search');

var rg=sh.getRange(sh.getActiveRange().getRow(),3,1,sh.getLastColumn()-2);

rg.activate();

return rg.getA1Notation();

}

function clearAll() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var rg=sh.getRange(2,3,sh.getLastRow()-1,sh.getLastColumn()-2);

rg.clear();

rg.setDataValidation(null);

}

function clearLastRow() {

var ss=SpreadsheetApp.getActive();

var sh=ss.getSheetByName('Search');

var rg=sh.getRange(getColumnHeight(3,sh,ss),3,1,sh.getLastColumn()-2);

rg.clear();

rg.setDataValidation(null);

}

function deleteAllNamedRanges() {

var ss=SpreadsheetApp.getActive();

var ui=SpreadsheetApp.getUi();

var nrA=ss.getNamedRanges();

var resp=ui.prompt("Are you sure that you want to remove all Named Ranges? This action is not reversible.",ui.ButtonSet.YES_NO);

if(resp.getSelectedButton()==ui.Button.YES) {

nrA.forEach(function(r,i){r.remove();});

}

}

onEdit.gs:

function onMyEdit(e) {

var o={value:e.value,range:e.range,columnStart:e.range.columnStart,rowStart:e.range.rowStart,rowEnd:e.range.rowEnd,columnEnd:e.range.columnEnd,oldValue:e.oldValue,source:e.source};

//e.source.toast('Entry');

var sh=e.range.getSheet();

if(sh.getName()=="Search") {

//e.source.toast('Enter Search');

if(e.range.columnStart==1 && e.range.rowStart==2 && e.value=="TRUE") {

e.source.toast('searchInit');

e.range.setValue("FALSE");

searchInit();

}

if(e.range.columnStart==1 && e.range.rowStart==3 && e.value=="TRUE") {

e.source.toast('Row Clear',null,-1);

e.range.setValue("FALSE");

launchClearDialog();

}

if(e.range.columnStart==1 && e.range.rowStart==4 && e.value=="TRUE") {

e.source.toast('Clear All',null,-1);

e.range.setValue("FALSE");

clearAll();

}if(e.range.columnStart==1 && e.range.rowStart==5 && e.value=="TRUE") {

e.source.toast('Clear Last Row',null,-1);

e.range.setValue("FALSE");

clearLastRow();

}

if(e.range.columnStart==3 && e.range.rowStart>1 && e.value.length>0) {

e.source.toast(Utilities.formatString('%s: %s',getHeaders(e)[e.range.columnStart],e.value),null,-1);

getDropDown({value:e.value,row:e.range.rowStart,col:e.range.columnStart+1});

}

if(e.range.columnStart>3 && e.range.columnStart<11 && e.range.rowStart>1 && e.value.length>0) {

e.source.toast(Utilities.formatString('%s: %s',getHeaders(e)[e.range.columnStart],e.value),null,-1);

getDropDown2(o);

}

if(e.range.columnStart==11 && e.range.rowStart>1 && e.value.length>0) {

e.source.toast(Utilities.formatString('%s: %s',getHeaders(e)[e.range.columnStart],e.value),null,-1);

}

}

}

function getHeaders(e) {

var hObj={};

var sh=e.range.getSheet()

var hA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0].forEach(function(e,i){if(e){hObj[i+1]=e;}});

return hObj;

}

utility.gs

function getColumnHeight(col,sh,ss) {

var ss=ss || SpreadsheetApp.getActive();

var sh=sh || ss.getActiveSheet();

var col=col || sh.getActiveCell().getColumn();

return sh.getRange(1,col,sh.getLastRow(),1).getValues().filter(String).length;

}

global.gs:

var ddEnum=Object.freeze({value:0,term:1,match:2});

Demo:

This video was taken with a slightly older version of the code.