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.

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?

Answer

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.

---

References:

Google Apps Script:

• Triggers
• Spreadsheet Class
• Event objects

JavaScript:

• forEach()
• filter()