Automatically add a check to a checkbox cell if a cell is Not blank
Hi all. I've been trying to figure this one out by myself but I just cant seem to get it.
I have a column "Date Closed" and I want to have a checkbox in that row be automatically checked once a value (date) is added to the "Date Closed" cell. (and the checkbox empty if there is no Date Closed value).
I thought this would be simple. I have been playing with this simple formula. can anyone help?
=IF(ISBLANK([Date Closed]:[Date Closed]), 0, 1)
I also thought it would be easier to not deal with the NOT statement, so I just switched the condition at the end. am I on the right track?
Best Answers
-
David Tutwiler Overachievers Alumni
Until you can get it to work, I would delete the second automation just to make sure they aren't accidentally interfering.
I can see that if you create a new row with the Date already filled in this automation won't work. It is only being triggered when the sheet is changed.
The other thing I can think of is that this automation requires that you save the sheet and then wait a few seconds for it to update. If you are making changes and waiting for the automation to run without saving the sheet, this won't work. I would suggest updating the date on a row, clicking Save, then waiting up to 1 minute (it should take just a few seconds) for the sheet to update.
If that doesn't work, will you let us know?
-
steves ✭David! thanks you were totally right. The automation is not immediate for some reason, it takes
This works great! thanks so much.
Answers
-
David Tutwiler Overachievers Alumni
You can do it with a formula, but you can also do it with automation. If you go into automation and select the Trigger as the Date Closed getting any value, then you can trigger and action to check a box in the correct column. Below is a screenshot of an example Automation to do the same thing.
-
steves ✭
Hi David! i tried your suggestion but it does not seem to be working. Here are my automations. Is there any reason you can think of that the cell won't automatically "Check" itself? i tried creating new rows, changing existing rows.
The second automation is for the cell to uncheck itself if the content in "date closed" is deleted
-
David Tutwiler Overachievers Alumni
Until you can get it to work, I would delete the second automation just to make sure they aren't accidentally interfering.
I can see that if you create a new row with the Date already filled in this automation won't work. It is only being triggered when the sheet is changed.
The other thing I can think of is that this automation requires that you save the sheet and then wait a few seconds for it to update. If you are making changes and waiting for the automation to run without saving the sheet, this won't work. I would suggest updating the date on a row, clicking Save, then waiting up to 1 minute (it should take just a few seconds) for the sheet to update.
If that doesn't work, will you let us know?
-
steves ✭
David! thanks you were totally right. The automation is not immediate for some reason, it takes
This works great! thanks so much.
-
David Tutwiler Overachievers Alumni
No problem, glad you got it going.
Help Article Resources
Categories
Check out theFormula Handbook template!
I hope you're well and safe!<\/p>
The easiest way is to create two Conditional Formatting Rules.<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/8WVTGA7DP26E\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nDid that work\/help? <\/p>
I hope that helps!<\/p>
Be safe, and have a fantastic week!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111262,"type":"question","name":"Automatically add a check to a checkbox cell if a cell is Not blank","excerpt":"Hi all. I've been trying to figure this one out by myself but I just cant seem to get it. I have a column \"Date Closed\" and I want to have a checkbox in that row be automatically checked once a value (date) is added to the \"Date Closed\" cell. (and the checkbox empty if there is no Date Closed value). I thought this would…","snippet":"Hi all. I've been trying to figure this one out by myself but I just cant seem to get it. I have a column \"Date Closed\" and I want to have a checkbox in that row be automatically…","categoryID":322,"dateInserted":"2023-10-05T16:46:03+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T14:08:52+00:00","insertUserID":157535,"insertUser":{"userID":157535,"name":"steves","url":"https:\/\/community.smartsheet.com\/profile\/steves","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T14:06:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-06T14:08:03+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":32,"score":null,"hot":3393128695,"url":"https:\/\/community.smartsheet.com\/discussion\/111262\/automatically-add-a-check-to-a-checkbox-cell-if-a-cell-is-not-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111262\/automatically-add-a-check-to-a-checkbox-cell-if-a-cell-is-not-blank","format":"Rich","lastPost":{"discussionID":111262,"commentID":398887,"name":"Re: Automatically add a check to a checkbox cell if a cell is Not blank","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398887#Comment_398887","dateInserted":"2023-10-06T14:08:52+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-06T14:08:03+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T14:07:06+00:00","dateAnswered":"2023-10-06T13:23:07+00:00","acceptedAnswers":[{"commentID":398868,"body":" Until you can get it to work, I would delete the second automation just to make sure they aren't accidentally interfering.<\/p> I can see that if you create a new row with the Date already filled in this automation won't work. It is only being triggered when the sheet is changed.<\/p> The other thing I can think of is that this automation requires that you save the sheet and then wait a few seconds for it to update. If you are making changes and waiting for the automation to run without saving the sheet, this won't work. I would suggest updating the date on a row, clicking Save, then waiting up to 1 minute (it should take just a few seconds) for the sheet to update.<\/p> If that doesn't work, will you let us know?<\/p>"},{"commentID":398886,"body":" David! thanks you were totally right. The automation is not immediate for some reason, it takes <minute for it to run and for the box to check itself. i was being impatient.<\/p> This works great! thanks so much.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111260,"type":"question","name":"Nested IF\/AND","excerpt":"I asked a question about this the other day, when I thought I had my formulas working, but as I continued to try to resolve the issue, I found that I didn't have the correct individual formulas. So, I'm posing the question again, providing the correct individual formulas (I hope). Individually, the formulas work and count…","snippet":"I asked a question about this the other day, when I thought I had my formulas working, but as I continued to try to resolve the issue, I found that I didn't have the correct…","categoryID":322,"dateInserted":"2023-10-05T16:25:28+00:00","dateUpdated":null,"dateLastComment":"2023-10-06T13:14:24+00:00","insertUserID":155140,"insertUser":{"userID":155140,"name":"Linda Hoydic","url":"https:\/\/community.smartsheet.com\/profile\/Linda%20Hoydic","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-06T11:47:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":131929,"lastUser":{"userID":131929,"name":"Todd M Keller","url":"https:\/\/community.smartsheet.com\/profile\/Todd%20M%20Keller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!evfS5_zpFr4!U-tTH2CLQXQ!1fO3vjLqVqQ","dateLastActive":"2023-10-06T13:14:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":51,"score":null,"hot":3393127192,"url":"https:\/\/community.smartsheet.com\/discussion\/111260\/nested-if-and","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111260\/nested-if-and","format":"Rich","lastPost":{"discussionID":111260,"commentID":398863,"name":"Re: Nested IF\/AND","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398863#Comment_398863","dateInserted":"2023-10-06T13:14:24+00:00","insertUserID":131929,"insertUser":{"userID":131929,"name":"Todd M Keller","url":"https:\/\/community.smartsheet.com\/profile\/Todd%20M%20Keller","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!evfS5_zpFr4!U-tTH2CLQXQ!1fO3vjLqVqQ","dateLastActive":"2023-10-06T13:14:48+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-06T13:38:13+00:00","dateAnswered":"2023-10-06T12:46:59+00:00","acceptedAnswers":[{"commentID":398850,"body":" Just to close the loop - I finally got it to work as I need it to. I think it was the order. Here is the final working formula string.<\/p> =IF(AND(Department@row = \"Cyber - SOC\", [Cancel Request]@row = \"Y\"), -NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row = \"Cyber - SOC\", [Department Head approval]@row = \"Approved\"), NETDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row <> \"Cyber - SOC\", [Cancel Request]@row = \"Y\"), -NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, IF(AND(Department@row <> \"Cyber - SOC\", [Department Head approval]@row = \"Approved\"), NETWORKDAYS([From Date]@row, [To Date]@row) - [Half Day Count]@row, 0))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
