Formula to check a box or to change status

Kelly S

Hi

Two of my columns are Status (dropdown options) and Done(checkbox). I currently have an automated work flow sending out alerts if the Done box is not checked. I only want my users to have to change the Status column to "Complete" and then the Done box automatically get checked.

I was trying this to no avail: =IF(([STATUS]@ROW)= 'COMPLETED', 1,0)

Obviously I'm new to Smartsheet and Formulas 😃. Your help is greatly appreciated.

Paul Newcome

Try removing the closing parenthesis after Status@row

=IF(Status@row = "Completed", 1)

Paul Newcome

You would need to enter it in each row whether that be through copy/paste or dragfilling.

Paul Newcome

Try something like this...

=IF(Status@row = "Complete", 1)

"@row" is most definitely case sensitive and must be lower case for it to work. You also do not need the parenthesis around the cell reference.

Finally... Be very careful with "Complete" vs "Completed". You have both listed in your post, and I can't even begin to count the number of hours I have spent banging my head against a wall trying to figure out why I was getting the wrong result from a formula only to realize that I was using the wrong one in the formula as compared to what I was using on my sheet.

Kelly S

thank you @Paul Newcome but unfortunately it is still not working. I liked your tip to be careful with Complete vs Completed. I could see doing that easily. I'm assuming "@row" just means on that same row, right?

I tried =IF([Status]@row)="Completed",1,0)

I tried =IF(Status@row)="Completed",1,0)

I tried =IF(Status@row)="Completed",1)

I made sure there were no spaces, ugh!

Paul Newcome

That is correct. When you say it is still not working, are you getting an error or unexpected results?

Kelly S

unparseable - no idea what that means

Paul Newcome

Unfortunately that is one of the most vague errors that can be thrown by many things. Let's start with this...

Can you provide a screenshot of the formula actually in the sheet similar to my screenshot below (be sure to include the column header for your Status column)?

Kelly S

Wow, thank you so much...yes, here is a snapshot

Kelly S

fyi - I checked manually the checkmark you see in pic

Paul Newcome

Try removing the closing parenthesis after Status@row

=IF(Status@row = "Completed", 1)

Kelly S

OMG - that's it. I had to add a ",0" at the end otherwise they all were checked mark but IT IS WORKING! Dare I ask you one more thing? Can I only put this formula on a row and then "copy/drag" it to all new rows or is there a way to do it at the Column level?

THANK YOU THANK YOU THANK YOU

Paul Newcome

You would need to enter it in each row whether that be through copy/paste or dragfilling.

Kelly S

Really appreciate you, thanks

Paul Newcome

Happy to help! 👍️

sdolan

Stupid question but I am trying to do something similar. Which column does the formula go in? I thought it would go in the column with the check box but I can't add text to a check box without the checkbox disappearing. Thanks