Populating a column based on checkbox combinations

✭✭

Hello!

I am interested in populating a column based checkboxes in other columns. This is the layout below. The column that would be populated is Extraction Method.

The Extraction Method column is a dropdown with four choices: Standard, Ampseq, Custom and Other. I would like certain combinations of the checkboxes to trigger one of these choices in that last column.

Here are the conditions:

Standard - when the ONLY box checked is in the Taqman (No Custom) column.

I used the formula =IF([Taqman (No Custom)]@row = 1, "Standard") for this one, but if other boxes are checked this should not return a result, so I need to add additional items to the formula, but am not sure what!

Ampseq - if SASi and/or LASi boxes are checked, but not Flank PCR or dPCR

I used the formula =IF(OR(SASi@row = 1, LASi@row = 1), "Ampseq") for this one also, but need to account for the case of Flank PCR and/or dPCR being checked. If Flank PCR and/or dPCR are checked, then this should not return a result. Again, I'm certain I'm missing something in the formula.

Custom - if Flank PCR and dPCR are checked, regardless of other checkboxes

Here I used the formula =IF(OR([Flank PCR]@row = 1, dPCR@row = 1), "Custom")

So, a few of these formulas need some work, I believe and then I would like to condense all of them into a single formula so that the right extraction method can be triggered based on what's in the checkboxes.

Thanks in advance for any help you can give me!

Jen

Best Answer

• ✭✭✭✭✭✭
Answer ✓

=IF(AND([Taqman (No Custom)]@row = 1, COUNT(SASI@row:dPCR@row)=0), "Standard", IF( AND(OR(SASi@row = 1, LASi@row = 1), dPCR@row=0, Flank@row=0), "Ampseq", IF(AND([Flank PCR]@row = 1, dPCR@row = 1), "Custom", "")))

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

• ✭✭✭✭✭✭
Answer ✓

=IF(AND([Taqman (No Custom)]@row = 1, COUNT(SASI@row:dPCR@row)=0), "Standard", IF( AND(OR(SASi@row = 1, LASi@row = 1), dPCR@row=0, Flank@row=0), "Ampseq", IF(AND([Flank PCR]@row = 1, dPCR@row = 1), "Custom", "")))

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭

There were a couple tiny tweaks I made, and it worked perfectly!

Thanks so much for your help and have a great weekend!!

Jen

• ✭✭✭✭✭✭

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!