Formula to uncheck one column when another column is checked

Angie Hatfield

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.

Andrée Starå

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!

Angie Hatfield

@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å

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)

Angie Hatfield

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

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

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å

@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?

Emmet McKenna

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å

@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?