Need help with the formula to update status based on changes to a field

Hi, I need help with a formula to automatically update status based on changes to multiple fields for example- I have =IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field

Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column.

I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column.


Tags:

Best Answer

  • Sam M.
    Sam M. ✭✭✭✭✭
    Answer ✓

    Hi Omotola,

    I did some testing and this formula might work for you:

     


    =IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

     

    Formula in sections based on what is asked:

    "=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

    IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

     

    "Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column."

    IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

     

    "I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

    IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

    Hope it helps,

    Sam

Answers

  • Sam M.
    Sam M. ✭✭✭✭✭

    Hi @Omotola Ashafa,


    I did some testing and this formula might work for you:

     

    =IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

     

    Formula in sections based on what is asked:


    "=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

    IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

     

    "Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column. "

    IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

     

    "I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

    IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

    Hope it helps,

    Sam

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭✭

    You should use an nested IF AND statement see below


    =IF(AND([Column7]@row > 0, [Column8]@row > 0, [Column9]@row > 0), "In Virtual Review", IF(AND([Column7]@row > 0, [Column8]@row > 0), "In Review", IF([Column7]@row > 0, "In Progress", "")))

  • Sam M.
    Sam M. ✭✭✭✭✭
    Answer ✓

    Hi Omotola,

    I did some testing and this formula might work for you:

     


    =IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review", IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review", IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")))

     

    Formula in sections based on what is asked:

    "=IF([Assigned To]@row <> "", "In Progress") to change status to In Progress when an input(name) is entered in the "assigned to" field"

    IF(NOT(ISBLANK([Assigned to]@row)), "In Progress", "Not Started")

     

    "Now, I want the status to change to "In Review" when a name is entered in the "Peer reviewer" column and there is already a name in the "Assigned to" column."

    IF(AND(NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Review")

     

    "I also need the status to change to "In Virtual Review" when a peer review complete is checked, and there's a name in the "Peer review" column as well as a name in the "Assigned to" column."

    IF(AND([Peer Review Complete]@row = 1, NOT(ISBLANK([Peer Review]@row)), NOT(ISBLANK([Assigned to]@row))), "In Virtual Review")

    Hope it helps,

    Sam

  • Thank you @Sam M. , it worked. Thank you for the explanation and for breaking it down.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!