Disable and Enable cells based on another cell in Google Spreadsheets

0
votes

The first column of my spreadsheet has dropdown lists in each cell with the options "yes" and "no".

If "yes" is selected, I want to enable the cell in the same row, next column with another dropdown list with options "red" and "blue". If "no" is selected, then the next cell should remain disabled.

So far I have tried the data validation menu with custom formula is set to =if(B2="Yes", "red", "blue") in cell B3, but it doesn't work.

How can I do this?

1
google-sheetsgoogle-spreadsheet-api
Check out youtube video's from Jason Jurotich here's a link to one. - Cooper

1 Answers

0
votes

You can do this with onEdit and script like this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var col=e.range.getColumn()
  var ro=e.range.getRow()
  var nota=e.range.getA1Notation()
  if(col==1){
  var val=e.range.getValue()
  if(val=="No"){
    s.getRange(ro,2,1,1).setDataValidation(null)
   }
      else{
      s.getRange(ro,2,1,1).clearContent()      
      var cell = s.getRange(ro,2,1,1);
      var rule =  
SpreadsheetApp.newDataValidation().requireValueInList(['Red', 'Blue']).build();
 cell.setDataValidation(rule);
      }}}

Here is a share example. Copy and try it.

https://docs.google.com/spreadsheets/d/1wHA1Rz9U9dr4n8DntLvZWPe4izck7xgi3ygUpx-uNmo/edit?usp=sharing