Revise Formula

Options

Far below is a screemsjpt of how I track whether or not students have met compliance.

Currently, they all start out as non-compliant when we add them to the sheet.

Once the last 2 columns are checked, the overall status outputs "Compliant",

Now I have an extra condition to add (Student health colume) and I'm not quite sure how to update the formula.

Here is the current formula (The 2nd IF statement is contingent on the first):

=IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), "Compliant", "Non-Compliant"))

I would like the results to be that if the last 2 columes (ACEMAPP and Advantage) are checked, the Overall Compliance Status output should be "Partially Compliant". If all 3 columes are checked, then the overall status output should be "Compliant".

Any help is greatly appreciated!

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/16/24
    Options

    @jgneely72151

    =IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?] = 1), "Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), “Compliant”, "Non-Compliant")))

    The parts I changed are bolded

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @jgneely72151

    I noticed a typo in my original post. I corrected it below.

    =IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?] = 1), "Partially Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), “Compliant”, "Non-Compliant")))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    Options

    I'm sorry; I'm getting unparseable.

  • jgneely72151
    jgneely72151 ✭✭✭✭✭
    Options

    Here are my results and the last one should show compliant when all fields are checked or has background completed. I also don't have the first If statement that marks the status as cancelled. It works by itself but not with the If statements. Not sure if something is missing. My notes are in the last column on how each worked when I select a flag or check boxes.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @jgneely72151

    =IF(OR([Flagged Designation]@row = "Revoked", [Flagged Designation]@row = "Deferred"), "Cancelled", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed", [Compliant w/Student Health?]@row = 1), "Compliant", IF(AND([Is ACEMAPP/ Complio Complete?]@row = 1, [AdvantageStudents Progress Status]@row = "✔ Background completed"), "Partially Compliant", "Non-Compliant")))

    I am not sure how you got the blue check marks. That could be part of the issue. I am only able to get green check marks. so you may have to fix that in the formula. how ever. This is the formula with a screen shot of it working.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!