Populating a column based on checkbox combinations

Jen Depp

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

Mark Cronk

=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

Mark Cronk

=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

Jen Depp

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

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

Jen

Mark Cronk

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

Mark