Formula to uncheck one column when another column is checked

Options

I have a checkbox column that is used when a project and any of it's tasks are Active.

I have another checkbox column that is used when a project and/or any of it's tasks are Done.

I have another column that will generate the date when the Done box is checked and also will automatically change the percent complete to 100%.

I am trying to figure out how to auto uncheck the Active column when the Completed column is checked or when % Complete is 100% or even when the date is auto filled.

I tried the following but it doesn't work. I get #INCORRECT ARGUMENT

=IF(Done1856, 1, Active1856, 0)

I have tried this within the Active column and a new column. I have made formulas in checkbox columns before, so didn't think this would be an issue.

Thank you for any assistance you can provide.

Answers

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

    Hi Angie,

    Try something like this.

    =IF(OR(Completed@row = 1; [% Complete]@row = 1; ISDATE(Date@row)); 1)

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

    =IF(OR(Completed@row = 1, [% Complete]@row = 1, ISDATE(Date@row)), 1)

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

    Did it work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it 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.

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    Options

    @Andrée Starå

    Good morning!

    I have attempted to use your formula. Two things. The formula does not remove the checkbox from the Active column. If the Active column is not checked when the Done column is checked, it then checks the Active column, which is opposite of what I need (need Active to uncheck). So I changed the formula.

    =IF(OR(Done1853 = 1, [% Comp]1853 = 1, ISDATE([Completed Date]1853)), 0)

    This then does not check the Active box (which is meant to be unchecked when Done, etc meets IF). However, I need the formula in the Active column prior to checking that column so Done triggers it to uncheck. When I click Active after entering the formula, it clears the formula. I don't recall this happening to me before.

    Maybe I need a formula in another column?

    Thanks so much for your help, I appreciate it very much!

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

    Happy to help!

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

    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.

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    Options

    Andrée,


    I just shared the sheet to you. It's titled Copy of Reporting Projects.

    Thank you very much for assisting me!

  • Emmet McKenna
    Emmet McKenna ✭✭✭✭
    Options

    Hi Angie/Andrée,

    Did you find a solution to the above problem? I want to do something similar in regards to ordering; a 'Ready to Order' checkbox being checked will populate that row in a Report, then a 'Ordered' checkbox being checked removes it from the report but I would also like it to remove the check from the 'Ready to Order' checkbox.

    Any help you could provide would be greatly appreciated.

    Thanks, Emmet

  • Angie Hatfield
    Angie Hatfield ✭✭✭✭
    Options

    Hi @Emmet McKenna

    I did not find anything satisfactory but haven't had time to look into it further.

    If you find a good solution, I would like to hear about it.

    Take care,


    Angie

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

    @Emmet McKenna

    @Angie Hatfield

    The only way I think it can work is with a 3rd party service like, Zapier or the Smartsheet API.

    Is that an option?

    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.

  • Emmet McKenna
    Emmet McKenna ✭✭✭✭
    edited 05/15/20
    Options

    HI Andrée, thanks for your quick reply.

    I have had a look at Zapier but I don't think it will work as the initial Trigger events are limited to:

    1. Updated Row (I don't think this is specific enough for what I need?)
    2. New Row
    3. New Attachment
    4. New Comment

    I am not that familiar with the Smartsheet API (but will look into it). Is there anything/where you would recommend starting on this?

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

    @Emmet McKenna

    Zapier would work for your use-case.

    You'd need to use the Updated Row feature.

    In short. When the Ordered checkbox is checked remove the Ready to Order checkbox.

    Updated Row

    Triggers when a row is updated.

    New Row Update

    Update an existing row with new values. Requires a row ID.

    Make sense?

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!