How can I trigger an IF statement only once?
I have a column that is a single select drop down menu. We use this to keep track of who is responsible for that line as the project moves through our departments. A couple of the selections are outside our company. We want to record in a separate column who the outside contractor is. I am attempting to copy the name of the outside contractor from the responsibility column to the outside contractor column the first time it is selected. Once it is copied into the outside contractor column I don't want to overwrite it again. I attempted to use IF statements and Automations, but I am unable to get it to work. Any help would be appreciated. Thanks.
Answers
-
Andrée Starå ✭✭✭✭✭✭Hi@Bobert
I hope you're well and safe!
How many Outside Contractors could you have?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and 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:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Bobert ✭✭The drop down has about 15 selections. 3 of those are outside contractors. We're trying to keep track of which outside contractor is working on the project, so that even when the work comes back, we know which one we sent it to.
-
Andrée Starå ✭✭✭✭✭✭
I'd recommend using a Workflow combined with the Assign People Action or similar.
Would that work/help?
✅Remember!我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and 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:[email protected]| 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
Categories
=IF(AND([Average Score]@row>= 5, [Average Score]@row<= 9), \"Project\", IF(AND([Average Score]@row>= 10, [Average Score]@row<= 15), \"Program\"))<\/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":110981,"type":"question","name":"Counting yes and yes w\/notes","excerpt":"Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called \"Yes w\/notes\". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = \"Yes\") \/ COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…","snippet":"Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called \"Yes w\/notes\". Here is the current formula that I have: • =COUNTIFS([Are all…","categoryID":322,"dateInserted":"2023-09-29T12:04:09+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T16:34:36+00:00","insertUserID":148583,"insertUser":{"userID":148583,"name":"ksandoval","url":"https:\/\/community.smartsheet.com\/profile\/ksandoval","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T16:57:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":15,"score":null,"hot":3391996125,"url":"https:\/\/community.smartsheet.com\/discussion\/110981\/counting-yes-and-yes-w-notes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110981\/counting-yes-and-yes-w-notes","format":"Rich","lastPost":{"discussionID":110981,"commentID":397702,"name":"Re: Counting yes and yes w\/notes","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397702#Comment_397702","dateInserted":"2023-09-29T16:34:36+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+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-09-29T13:48:27+00:00","dateAnswered":"2023-09-29T13:35:30+00:00","acceptedAnswers":[{"commentID":397634,"body":"
Try this:<\/p>
=COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, OR(@cell = \"Yes\", @cell = \"Yes w\/Notes\")) \/ COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell <> \"n\/a\")<\/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":110978,"type":"question","name":"IndexCollect \/ JoinCollect for Multi Select Columns","excerpt":"I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…","snippet":"I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT…","categoryID":322,"dateInserted":"2023-09-29T08:50:47+00:00","dateUpdated":null,"dateLastComment":"2023-09-29T16:38:43+00:00","insertUserID":163620,"insertUser":{"userID":163620,"name":"Samantha McDonald","url":"https:\/\/community.smartsheet.com\/profile\/Samantha%20McDonald","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-29T15:04:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3391984770,"url":"https:\/\/community.smartsheet.com\/discussion\/110978\/indexcollect-joincollect-for-multi-select-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110978\/indexcollect-joincollect-for-multi-select-columns","format":"Rich","lastPost":{"discussionID":110978,"commentID":397706,"name":"Re: IndexCollect \/ JoinCollect for Multi Select Columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397706#Comment_397706","dateInserted":"2023-09-29T16:38:43+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-09-29T16:56:30+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-09-29T14:30:55+00:00","dateAnswered":"2023-09-29T13:32:47+00:00","acceptedAnswers":[{"commentID":397633,"body":"
For your first one with the DISTINCT function, you just need to move one of the closing parenthesis from the very end to to after [PRODUCTS OTHER]@row. You need to close the DISTINCT function before you enter your delimiter for the JOIN function.<\/p>
<\/p>
For the second piece, you would \"add\" the INDEX\/COLLECT to the JOIN.<\/p>
=INDEX(...........) + \" , \" + JOIN(..........)<\/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":[]}">
