IF FORMULA TO PUT AUTOMATIC STATUS

07/31/19 Edited 12/09/19

I would like to know what can I do to put automatic status for my monitoring sheet?  There are 3 status:  In Transit, Cancelled and Delivered.  I want to know if its possible that when the requestor input the delivery date (refer to the screenshot), the status will change to "Delivered".  For the Cancelled and Transit though, is it possible that even if the delivered status is formulated, putting In Transit and Cancelled is manual dropdown?

Hope someone can help me.  Thanks.

Capture.PNG

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Mariniel,

    Yes, it is, but when you change it manually to Cancelled or Delivered, it will remove delete the formula, but it seems like that shouldn't be a problem because it's after "Date of Delivery."

    Try something like this.

    Add the formula below to the Status column.

    =IF(ISDATE([Date of Delivery]@row); "In Transit")

    The same version but with the below changes for your and others convenience.

    =IF(ISDATE([Date of Delivery]@row), "In Transit")

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Mike WildayMike Wilday ✭✭✭✭✭

    I don't know if I agree with Andree. Your status column as a dropdown has in transit and Delivered. If delivered is the LAST leg of the journey, as it appears it is. Any manual input of the dropdown will override your formula. I would recommend putting in checkbox columns for in transit and canceled, and then lock the dropdown column and use this formula: 

    =IF(ISDATE([Date of Delivery]@row), "Delivered", IF([email protected] = 1, "Cancelled", IF([In Transit]@row = 1, "In Transit")))

    This will make your status column completely a formula, but also allow for each checking off of items in transit and canceled. 

  • Hi Mike.  I will try your suggestion and check if it will work.  Thank you so much.  I will give update once I am done with the checking.

  • Hi Mike.  I tested it and it worked.  Thank you so much for your help!

  • Mike WildayMike Wilday ✭✭✭✭✭

    Awesome! So glad I could help you out. Have a great day! 

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    I clearly misunderstood! Sorry about that!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.