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
-
=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
-
=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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!