Checkbox for Envelope Status Column

I have a column called "Envelope Status" for tracking SS generated DocuSign docs. I also have a column that is a checkbox and for that column, I'd like to write a formula so when the status is certain values it is checked and stays checked.

The possibilities for "Envelope Status" are:

Blank (meaning nothing is in the column)

Draft

Sent

Delivered

Declined

Voided

Completed

My question is, do I write the formula so it checks and stays checked if it's Sent, Delivered, Declined, Voided or Completed, and if so, how many values can be in an OR function? The following formula works for two, but when I try to add more values it does not. And the box stays checked if I delete a value it had, meaning it won't uncheck with I make the cell blank.

=IF(OR([Envelope Status]@row = "Sent", [Envelope Status]@row = "Delivered"), 1, 0)

Or, do I write the formula using a NOT function, thus saying, check the box if it is NOT blank or Draft and if so, what is the format of the formula? My attempts with IF(NOT) only work with one value.


Thanks

Best Answer

Answers

  • Christina09
    Christina09 ✭✭✭✭✭✭

    There is an automation called "changed cell value". You can something like below:

    Hope that works

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    Thank you @Christina Lam. That option works in part. It does not remove the check if the Envelope status is changed to blank or in the case of the Received checkbox column when it changes to Sent. It is also slow to update.

    I'd really like to use a formula because that is immediate and it removes the check with things change. Not doing so could make our reports on these inaccurate.

    Do you know a way to do this with a formula or to get the checkmark to go away when the statutes changes to nothing?

    thanks

  • Christina09
    Christina09 ✭✭✭✭✭✭

    @Andrea Westrich

    Sorry, maybe I misunderstood.

    Try this:

    =IF([Envelope Status]@row = "sent", 1, IF([Envelope Status]@row = "delivered", 1, IF([Envelope Status]@row = "voided", 1, IF([Envelope Status]@row = "completed", 1))))

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Andrea Westrich

    I hope you're well and safe!

    To add to Christina's excellent advice/answer.

    Try something like this.

    =IF(AND([Envelope Status]@row <> "", [Envelope Status]@row <> "Draft"), 1)
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrea Westrich
    Andrea Westrich ✭✭✭✭✭✭

    @Andrée Starå That works great, thank you!!

    Question, can the AND function work for 3 criteria or is it limited to 2? Meaning, if I want the checkbox not to check if the Envelope Status blank, Draft or Sent, could that be incorporated and work? I did try it and it checked the box when Envelope Status was blank, like it skipped the first criteria.

    I appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!