Dropdown list based on the result of another cell's dropdown

Question

I want to populate a dropdown list based on a selection of another cell's dropdown list.

Here is my spreadsheet.

E.g., if the user does not make a choice from D20 dropdown menu, therefore F20 dropdown is Blank but has the option of only ChoiceA in its dropdown menu.

If User does make a choice from the D20 drop-menu, therefore F20 is Blank but has options of ChoiceA, ChoiceB, and ChoiceC in its dropdown menu.

I've played around with the below but it's not quite right...

This formula is in cell Sheet2!B:14

=ARRAYFORMULA(IF(ISBLANK(Sheet1!D20),(Sheet2!A14),(Sheet2!A14:A16)))

and my Sheet1!F20 cell uses data validation list from range Sheet2!B14:B16

On a single line it works as needed, but when rows are added issue that arose is once a selection is made anywhere in column D that allows for all 3 choices in the F column drop-menu, then all 3 choices become permanently available in any added F column cells regardless of column D choice in the same row.

Another issue needing a fix is if the option in Column D is deleted, all three options are also still available - although a warning comment is present.

Can these issues be solved either with formula or script?

Answer 1

here is a simple formula solution:

Sheet1:

D20: Data Validation > List of items: I made a choice,I didn't make a choice

F20: Data Validation > List from a range: Sheet2!D8:D11

Sheet2:

D8: =IF(Sheet1!D20 = B6; QUERY(B8:B11;;-1);IF(Sheet1!D20 = C6; QUERY(C8:C11;;-1);))

Answer 2

If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.

But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.

What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.

In the script, e.range is the cell edited, e.value is its new value.

function onEdit(e) {
  if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) {  // only Column D, not header 
    var optionA = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA'], true).build();
    var optionsABC = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();

    var affectedCell = e.range.offset(0,2,1,1);  // two steps to the right
    if (e.value === '') {
      if (affectedCell.getValue() !== 'OptionA') {
        affectedCell.setValue('');    // reset if current choice is no longer legal
      }
      affectedCell.setDataValidation(optionA); 
    }
    else {
      affectedCell.setDataValidation(optionsABC);      
    }
  }
}