Google-sheets – Added rows in Google Sheets missing formula

In array formulas instead of AND and OR functions use * and + respectively.

Example

AND: Instead of

=ArrayFormula(and(A2:A4,B2:B4))

use

=ArrayFormula(A2:A4*B2:B4)

OR: Instead of

=ArrayFormula(or(A2:A4,B2:B4))

use

=ArrayFormula(A2:A4+B2:B4)

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);      
    }
  }
}