How can I trigger an IF statement only once?

Bobert
Bobert ✭✭
inFormulas and Functions

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.

Tags:
· Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
I discovered that it is possible through the function \"=SUM(CHILDREN())\"<\/p>
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/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":110966,"type":"question","name":"Formula error","excerpt":"I want to create a sheet summary of some data. One formula that will count the number of cells in the Status column that contain \"SPEC\" if the Closed to Builder Box NOT checked. One formula that will count the number of cells in the Status column that contain \"SPEC\" if the Closed to Builder Box NOT checked.","snippet":"I want to create a sheet summary of some data. One formula that will count the number of cells in the Status column that contain \"SPEC\" if the Closed to Builder Box NOT checked.…","categoryID":322,"dateInserted":"2023-09-28T21:42:00+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T22:42:29+00:00","insertUserID":166427,"insertUser":{"userID":166427,"name":"tmartin","url":"https:\/\/community.smartsheet.com\/profile\/tmartin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T23:23:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":166427,"lastUser":{"userID":166427,"name":"tmartin","url":"https:\/\/community.smartsheet.com\/profile\/tmartin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T23:23:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3391879469,"url":"https:\/\/community.smartsheet.com\/discussion\/110966\/formula-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110966\/formula-error","format":"Rich","lastPost":{"discussionID":110966,"commentID":397556,"name":"Re: Formula error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397556#Comment_397556","dateInserted":"2023-09-28T22:42:29+00:00","insertUserID":166427,"insertUser":{"userID":166427,"name":"tmartin","url":"https:\/\/community.smartsheet.com\/profile\/tmartin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T23:23:45+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\/LA0RXDMZ1BO4\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-29T08:50:16+00:00","dateAnswered":"2023-09-28T21:54:36+00:00","acceptedAnswers":[{"commentID":397555,"body":"

To count the number of rows with Status<\/strong> of \"SPEC<\/strong>\" and Closed to Builder<\/strong> box is NOT <\/strong>checked:<\/p>

=COUNTIFS(Status:Status, \"SPEC\", [Closed to Builder]:[Closed to Builder], 0)<\/p>

To count the number of rows with Status<\/strong> of \"SPEC<\/strong>\" and Closed to Builder<\/strong> box IS <\/strong>checked:<\/p>

=COUNTIFS(Status:Status, \"SPEC\", [Closed to Builder]:[Closed to Builder], 1)<\/p>


<\/p>

(You can place these formulas in the fields on your Sheet Summary.)<\/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":110959,"type":"question","name":"If formula for multiple check boxes.","excerpt":"I have multiple columns with check boxes and I need a new column that validates if ALL are checked. I'm trying to create a formula that says: if column 2 is checked, AND column 3 is checked, AND column 4 is checked, 'yes' else 'no'. Any help is appreciated.","snippet":"I have multiple columns with check boxes and I need a new column that validates if ALL are checked. I'm trying to create a formula that says: if column 2 is checked, AND column 3…","categoryID":322,"dateInserted":"2023-09-28T20:31:12+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T21:40:55+00:00","insertUserID":167679,"insertUser":{"userID":167679,"name":"JoC","url":"https:\/\/community.smartsheet.com\/profile\/JoC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T21:40:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":167679,"lastUser":{"userID":167679,"name":"JoC","url":"https:\/\/community.smartsheet.com\/profile\/JoC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T21:40:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":24,"score":null,"hot":3391872727,"url":"https:\/\/community.smartsheet.com\/discussion\/110959\/if-formula-for-multiple-check-boxes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110959\/if-formula-for-multiple-check-boxes","format":"Rich","lastPost":{"discussionID":110959,"commentID":397550,"name":"Re: If formula for multiple check boxes.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397550#Comment_397550","dateInserted":"2023-09-28T21:40:55+00:00","insertUserID":167679,"insertUser":{"userID":167679,"name":"JoC","url":"https:\/\/community.smartsheet.com\/profile\/JoC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T21:40:22+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-28T21:40:46+00:00","dateAnswered":"2023-09-28T21:38:08+00:00","acceptedAnswers":[{"commentID":397549,"body":"

You have an extra comma in your formula before the close parenthesis for the AND<\/p>

\n
\n \n \"example.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Once you remove that, you should be set!<\/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":[]}">

Trending in Formulas and Functions

  • Sum of rows in hierarchy

    Hello everyone! How can I sum rows of duration (minutes or hours) in a hierarchy? I add an example: I need sum of total hours for every group. Thanks to who will help me ;-)

  • Formula error

    I want to create a sheet summary of some data. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked.

  • If formula for multiple check boxes.

    I have multiple columns with check boxes and I need a new column that validates if ALL are checked. I'm trying to create a formula that says: if column 2 is checked, AND column 3 is checked, AND column 4 is checked, 'yes' else 'no'. Any help is appreciated.