Is there a way to check a box in a sheet based on data and checkbox in another sheet?
In this scenario, Index Smartsheet automation is to happen based on data in the Licensing Smartsheet. More specifically - Check box in Ready To Start E. cell in Index Smartsheet when Licensing Smartsheet States To Be Filed (checkbox) is checked and ALL states to be filed Filing Status's equal “Complete” and ALL states to be filed State is one of or equal to or matches state in Required States column.
Best Answer
-
Genevieve P. Employee AdminThe second formula should only check to see if the Required States columnhasthe values that the JoinCollect formula brought back... even if those values are selected with other ones.
Did you try this?
=IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)
In my image, the "Formula Column" is called "JoinCollect" so my formula would be:
=IF(HAS(JoinCollect@row, [Required States]@row), 1, 0)
Answers
-
Genevieve P. Employee Admin
Yes!
Assuming that the "Required States" column is a multi-select column, what we can do is add in another helper column to your "Index Smartsheet" sheet.
Make the helper column another multi-select column, then we can use aJOIN(COLLECT formula to bring back all of the States that meet your criteria into one cell (the same cell, copied down the entire sheet).
Then we use the HAS function to see if this cell that lists every Statehasall of your "Required States" listed for that row.
Here's an example where I built this in the same sheet. Pretend the blue columns are in a different sheet:
A cross-sheet JOIN(COLLECTcolumn formulawould looks something like this:
=JOIN(COLLECT({States Column}, {Filing Status Column}, "Complete", {Sates to be filed Column}, 1), CHAR(10))
The CHAR(10) at the end is what separates your values into a multi-select value.
Then in your checkbox column, try a formula like this:
=IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)
Cheers,
Genevieve
-
Hi@Genevieve P.I am sorry for the delayed response. Thanks for the recommendation. It works, but...
In the instance where Required States row containing AL, AK, AR, CO, I need the Ready box to be checked. My reason for this is because AL , CO & CT need to be ignored since they are not checked in States To Be Filed. Is this possible?
Tony
-
Genevieve P. Employee Admin
The second formula should only check to see if the Required States columnhasthe values that the JoinCollect formula brought back... even if those values are selected with other ones.
Did you try this?
=IF(HAS([Formula Column]@row, [Required States]@row), 1, 0)
In my image, the "Formula Column" is called "JoinCollect" so my formula would be:
=IF(HAS(JoinCollect@row, [Required States]@row), 1, 0)
-
Help Article Resources
Categories
Check out theFormula Handbook template!
{Cross Sheet Reference}<\/p>
<\/p>
with<\/p>
[Column name]:[Column Name]<\/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":112222,"type":"question","name":"Count Days and Durations","excerpt":"I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from start date to today, but to stop counting days when finish date is reached.","snippet":"I am looking to count days used in my schedule per task. I am set up with start and finish date columns along with a duration column. I am trying to calculate days on site from…","categoryID":322,"dateInserted":"2023-10-26T17:03:59+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T15:15:39+00:00","insertUserID":164388,"insertUser":{"userID":164388,"name":"Caleb W","title":"Operations Leader","url":"https:\/\/community.smartsheet.com\/profile\/Caleb%20W","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AFdZucpGLwolOKyzvAL1Qwa-C_qqnmxB9j9mfmL4qM8Hnw=s96-c","dateLastActive":"2023-10-27T17:19:12+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":113581,"lastUser":{"userID":113581,"name":"Matthew J McAteer","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20J%20McAteer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDFUs7dXyHo!7bFXOzNnVPw!fm5dCHPC2D9","dateLastActive":"2023-10-27T16:03:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":39,"score":null,"hot":3396762578,"url":"https:\/\/community.smartsheet.com\/discussion\/112222\/count-days-and-durations","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112222\/count-days-and-durations","format":"Rich","lastPost":{"discussionID":112222,"commentID":402031,"name":"Re: Count Days and Durations","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402031#Comment_402031","dateInserted":"2023-10-27T15:15:39+00:00","insertUserID":113581,"insertUser":{"userID":113581,"name":"Matthew J McAteer","url":"https:\/\/community.smartsheet.com\/profile\/Matthew%20J%20McAteer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zDFUs7dXyHo!7bFXOzNnVPw!fm5dCHPC2D9","dateLastActive":"2023-10-27T16:03:18+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/JDZDOA0P3L7I\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:19:33+00:00","dateAnswered":"2023-10-26T22:22:24+00:00","acceptedAnswers":[{"commentID":401941,"body":"
