CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formula
Randal Andrade Nunes
Randal Andrade Nunes

Reputation: 4884

How to make Google Sheet drop down value affect other drop down

I have 4 drop downs with TRUE or FALSE options. I am looking to make a formula that makes it so that if one of them is true the rest is set to false automatically.

enter image description here

So, in this case since Next Day is TRUE, sameday, sd 11am, rush and P2P are false. If I change Sameday to TRUE, I would like Next Day to change to False automatically. Is that possible?

Upvotes: 1

Views: 464

Answers (1)

Marios
Marios

Reputation: 27390

Explanation:

  • You can't achieve that by using a google sheet formula.
  • You clearly need an onEdit() trigger function and therefore take advantage of Google Apps Script.
  • As soon as a cell in the list ['F2','G2','H2','I2','J2'] is edited, the script will check which cell was edited and whether the new value is TRUE or FALSE. If the value is TRUE then it will change the values of the other cells to FALSE. If the newly selected value is FALSE then it won't do anything.

Solution:

Please follow the instructions (gif). They are pretty straightforward.

Here is the code snippet:

function onEdit(e) {

  const cellR = e.range.getA1Notation()
  const as = e.source.getActiveSheet();
  const sheetName = "Sheet1";
  const cells = ['F2','G2','H2','I2','J2'];
  
  cells.forEach(cell=>{
  var otherCells = cells.filter(val=>val!=cell);
  if(as.getName() == sheetName && cellR ==cell && as.getRange(cell).getDisplayValue() == "TRUE"){
  otherCells.forEach(o_cells=>{as.getRange(o_cells).setValue("FALSE")})}}
  );   
}

Please adjust the name of the sheet (sheetName) to the name of your own sheet.

Instructions/Demonstration:

Please follow these instructions on how to set it up and use it:

  • click on Tools => Script editor,
  • copy and paste the code snippet to the code.gs file,
  • and finally click on the save button.

instructions

References:

Google Apps Script:

JavaScript:

Upvotes: 4

Related Questions