Check Box in one sheet based off value in another sheet


I would like to have a check box checked based off either the Status complete in another sheet or the percentage complete on another sheet. One or the other not both.

What would be the formula?



  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭
    edited 06/27/23

    HI @betancese, the devil is in the details, but here's a formula that would work based off my understanding of your ask:

    = IF(OR(INDEX(COLLECT({StatusComplete}, {Project ID}, ProjectID@row),1)="Complete", INDEX(COLLECT({Percentage Complete}, {Project ID}, ProjectID@row),1)=1), 1, 0)

    You need a unique identifier to reference, so I am calling that unique reference the Project ID in both the source and target sheets. I'm assuming you are looking for a "Complete" status, or that you are looking for a percent complete of 100%, which is equal to one ("1").

    You would enter this formula in a Checkbox column. The "1" in the true portion of the IF statement means "Checked" and the "0" means "Not Checked".

    Good luck!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!