How to create a formula where if multiple criteria is met, a checkbox is marked?
I am pretty new to using smartsheet formulas, so bear with me! I am trying to create a formula where if multiple cells contain certain text, a checkbox in another cell will be checked off. I want the checkbox to only be marked if multiple criteria are exactly met. If all criteria is not met, I would like it to remain unchecked. I will copy below a screenshot of what I am working with.
Basically, we have students who complete several tasks (comps) for segments. On the image above, segment 1 consists of the columns in the yellow highlighted box (Incomplete Comps-Climbing 1, Incomplete Comps-Distribution 1, Incomplete Comps-Underground, and Incomplete Comps-Equipment 1). Currently, we have it set up where if a student has not completed tasks, the task title is listed in the respective cell. If a student does not have any titles listed and only commas, then he is complete.
For example: The student in the white row is fully complete, so I am wanting the first column, Segment 1 COMPLETE to be checked. The second student in grey is not complete, so I want the checkbox to NOT be marked.
Is this something that is possible within smartsheets? I have tried the following formulas with no luck.
=IF([Incomplete Comps - Climbing 1]@row,", , , , , ,",IF(AND([Incomplete Comps- Distribution 1]@row,",,,,,,")(AND([Incomplete Comps- - Underground]@row,",,,,,,,,,,")(AND([Incomplete Comps- Equipment 1]@row,",,,")=true
=IF([Incomplete Comps - Climbing 1]@row = ", , , , , ," + [Incomplete Comps- Distribution 1]@row = ",,,,,," + [Incomplete Comps- - Underground]@row = ",,,,,,,,,," + [Incomplete Comps- Equipment 1]@row = ",,,") = true
If that sort of thing is possible, I would like to add this to each other segment completion checkbox for their resptive comps as well, and also add the prerequisite that the segment 1 checkbox must be checked as well. Would this be something that smartsheet can do?
Thank you so much for your help! I apologize if this message was a bit confusing- I am still learning.
Help Article Resources
Check out the Formula Handbook template!