Google Sheets formula to change a cells value base

问题:

I am currently muddled while trying to create a formula within Google Sheets that basically says:

"If the value of cell E10 equals "Levy" then change the value of E11 cell to "Monthly", if E10 equals anything other than "Levy" change the value of E11 to a drop down of menu "Triannual" and "Quarterly"*

This is the formula I have so far which gets the first part done. I'm just stuck with creating the formula to create the drop down of the two other options if E10 = something other than the specified value.

= IF(E10 = "Levy","Monthly","Triannual")

What else would I need to add?

Best Regards.

回答1:

This should do it. Copy this to your script editor and save it:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="E10"){
  var val=e.range.getValue()
    if(val=="Levy"){
      s.getRange("E11").setDataValidation(null)
      s.getRange("E11").setValue("Monthly")
  }
      else{
  s.getRange('E11').clearContent()      
  var cell = s.getRange('E11');
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Triannual', 'Quarterly']).build();
  cell.setDataValidation(rule);
   }}}