Dependent Drop Down Lists

Question

I'm currently devising a dependent drop down list which I've set up using the Indirect function. In the 'I' column I have a drop down consisting of 'Livery' and 'Operator' and the dependent drop down is in the 'K' column which lists the relevant detail dependent on whether Livery or Operator is selected.

However, I have a 3rd item to add to the drop down in 'I' column which is 'Name' but if selected I would want this to require a manual input into the 'K' column - is it possible to have sheets give me a dependent drop down if Livery or Operator is selected but a blank cell for manual input if Name is selected?

Answer 1

Your issues can be resolved by using Apps Script and writing a script.

The first function will be to create the drop down menu with the values you wanted using the .newDataValidation() method.

function createDropdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(['Livery','Operator','Name'],true).build();
  ss.getRange('I1').setDataValidation(rangeRule);
}

The second function will be used to update the I cell based on the selection from the K cell.

//the installable trigger 
function onEditTrigger(e) {
    var sheet = e.range.getSheet();
    var cellValue = e.range.getValue();
    var liveryOptions = ['livery1', 'livery2', 'livery3', 'livery4'];
    var operatorOptions = ['operator1', 'operator2', 'operator3', 'operator4'];

    if (cellValue == 'Name') {
      SpreadsheetApp.getUi().alert('Input your name in the K1 cell');
      sheet.getRange("K1").setDataValidation(null);
      sheet.getRange("K1").clear();
      sheet.getRange("K1").setBackground("yellow");
    } else if (cellValue == 'Operator') {
      sheet.getRange("K1").setBackground("white").setValue("Choose an option");
      var dataValidation = SpreadsheetApp.newDataValidation().requireValueInList(operatorOptions, true).build();
      sheet.getRange("K1").setDataValidation(dataValidation);
    } else if (cellValue == 'Livery') {
      sheet.getRange("K1").setBackground("white").setValue("Choose an option");
      var dataValidation = SpreadsheetApp.newDataValidation().requireValueInList(liveryOptions, true).build();
      sheet.getRange("K1").setDataValidation(dataValidation);
    }
}

In order for your drop down selection to work, you should add an installable trigger to the second function.

You can go to your project's triggers and create a new trigger with the following properties:

When you run the script, you will only have to run the createDropdown() function as the trigger will run continuously in the background.

Here is how the script works:

When Livery is selected

When Name is selected an Alert prompt will show up asking the user to input the data on the cell

Furthermore, here are some links that might help you:

1. Installable Triggers;

2. DataValidation Class;

3. Apps Script.