Need assistance 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 A, B, and C are blank, I want the status to automatically change to "Not started."

If Columns A or B are not blank, I want the status to automatically change to "In progress."

If Column C is not blank, I want the status to automatically change to "Complete."

Also, I currently have the status column set as a drop down menu. Would I need to delete this in order to use a formula? If I use a formula, I would no longer need to manually change this column.

Any assistance is greatly appreciated!

Tags:

Answers

  • Hi Brooks,

    This is my first time answering on here, but have been using this forum for several months now with great success.

    I came across a similar issue to what you have and decided to use a formula instead. Multiple IF statements will help you achieve an automated response/ update. As mentioned, removing the need for a drop-down.

    I would love to see the ability to have both; query running over the column unless a drop down is selected but I can't seem to figure if this is even possible.

  • Brooks
    Brooks ✭✭✭✭

    Hi Rob Cooper,

    Thanks for your quick reply! Can you be more specific in your answer, by chance? I am new to complex formulas. Would it be possible for you to share the formula that worked for you?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/27/21

    Hi @Brooks 

    Hope you are fine, please try the following formula and convert it to a column format formula

    =IF(AND(ISBLANK(A@row), ISBLANK(B@row), ISBLANK(C@row)), "Not started", IF(C@row > 0, "Completed", "In Progress"))

    the following screenshot shows the result


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!