Need help creating a formula that automatically changes the "status" based on several fields

Hi! I need help with a formula to automatically update the "status" column based on changes to multiple fields.

The status column has three options: Not started, In progress, and Complete.

-- If columns "Reviewer Assigned" and "Approver Assigned" are not blank, I want the status to automatically change to "In progress."

-- If column "Send Email?" is "Yes", I want the status to automatically change to "Complete."

-- Otherwise, the stsus would be "Not Started" (ie. no reviewer or approver).

Here is what I have so far. I know there are errors though:

=IF(NOT(ISBLANK([Reviewer Assigned]@row)),NOT(ISBLANK([Approver Assigned]@row)),"In progress",IF([Send Email?]@row="Yes","Complete","Not started"))))

Thank you for your help!


Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Brooks ,

    You're very close! Try this:

    =if(and([reviewer assigned]@row<>0,[approver assigned]@row<>0),"In Progress",if([send email?]@row="Yes","Complete","Not Started"))

    Let me know if it works.




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!