I want to prevent user from choosing data from the dropdown inside cell A if checkbox in cell B OR cell C is FALSE.

Even better, if it's possible to not even show the dropdown menu in cell A if checkboxes in B OR C are FALSE.

Dropdown would be disabled by default when B or C are FALSE (also default) and would show up when B OR C would be ticked and changed to TRUE.

Columns B and C are FALSE by default (unchecked actual checkbox).

Is it possible to make this in Google Spreadsheets?

CodePudding user response：

Using apps script:

Using app script try the following code. This uses onEdit trigger which runs everytime you edit a cell. It then checks the condition -> if cell B and cell C are both true only then it will show the dropdown list on cell A, if changed to false it will be removed again.

function onEdit(e) {
  var spreadsheet = e.source;
  var sh = spreadsheet.getActiveSheet();
  var rng = spreadsheet.getActiveRange();
  var rngRow = rng.getRow();
  var rngCol = rng.getColumn();

  var options = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OPTIONS").getRange("A2:A") //set sheetname and cell range where the options are coming from
  var dropdownCell = sh.getRange(rngRow, 1);

  var otherCol = sh.getName() == 'Sheet1' ? rngCol == 2 ? 3 : 2 : '';
  var [cell, otherCell] = sh.getRangeList([`R${rngRow}C${rngCol}`, `R${rngRow}C${otherCol}`])
    .getRanges().map(range => range.getValue());

  if (rngCol == 2 || rngCol == 3) {
    if (cell && otherCell) {
      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(options)
        .build();
      dropdownCell.setDataValidation(rule);
    } else {
      dropdownCell.setDataValidation(null);
    }
  }
}

Result:

The options for data validation are coming from the other tab/sheet "OPTIONS", starting on row 2.

CodePudding user response：

Here's a toy example of how to do this without Apps Script. Obviously you would need to modify to suit your requirements.

In A1, switch on 'List from a range' data validation, and set the range to 'D1:G1'. In B1 & C1, add tick boxes. In D1, enter the following formula:

=if(or(B1,C1),{"A","B","C","D"},)

So whenever B1 or C1 is ticked, an array literal is expanded into D1:G1, and the contents of these cells populate the data validation list for A1. When neither is ticked, D1 is null and no validation list is available. Obviously the validation range doesn't need to be in D1; anywhere (even another sheet) will work provided you point the data validation in A1 to the right range.