Health based on check box

Options

Hi Community,

I want to automate my health column, based on whether several checkboxes (milestones) are ticked or not. Health column is indicated in heart symbols ("Empty" - "Five").

That's what I have right now, but causing an error:

=IF([Infra Team informed to remove legacy hardware]@row = 1, "One", IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1) "Two", IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1) "Three", IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1)"Four", "Empty")))))

Best Answer

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓
    Options

    If it is the later. I would actually reverse your order. The way the nested if statements work is if the criteria in the first statement is true then it will stop there, i.e. any that have the [Infra Team informed to remove legacy hardware] checked would show as 1 heart.

    This formula should work...

    =IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1),"Four",IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1), "Three",IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1), "Two", IF([Infra Team informed to remove legacy hardware]@row = 1, "One","Empty"


    As I was doing that I actually noticed that you missed a couple of commas so if it is throwing an error that might have been it also.

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Options

    Is it giving an actual error or just giving you 1 heart or empty?

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭
    Answer ✓
    Options

    If it is the later. I would actually reverse your order. The way the nested if statements work is if the criteria in the first statement is true then it will stop there, i.e. any that have the [Infra Team informed to remove legacy hardware] checked would show as 1 heart.

    This formula should work...

    =IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1,[Cutover completed]@row=1),"Four",IF(AND([Infra Team informed to remove legacy hardware]@row=1,[Back Office Printer deployed]@row=1,[Smoke Testing completed]@row=1), "Three",IF(AND([Infra Team informed to remove legacy hardware]@row=1, [Back Office Printer deployed]@row=1), "Two", IF([Infra Team informed to remove legacy hardware]@row = 1, "One","Empty"


    As I was doing that I actually noticed that you missed a couple of commas so if it is throwing an error that might have been it also.

  • Thanks! It works. Logical thinking error on my side :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!