Formulas and Functions

\"//www.santa-greenland.com/community/categories/formulas-and-functions/\", \"In Progress\") to change status to In Progress when an input(name) is entered in the \"assigned to\" field Now, I want the status to change to \"In Review\" when a name is entered…","categoryID":322,"dateInserted":"2021-03-16T14:24:38+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T21:35:00+00:00","insertUserID":132579,"insertUser":{"userID":132579,"name":"Omotola Ashafa","url":"https:\/\/community.smartsheet.com\/profile\/Omotola%20Ashafa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-04-07T15:26:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":132579,"lastUser":{"userID":132579,"name":"Omotola Ashafa","url":"https:\/\/community.smartsheet.com\/profile\/Omotola%20Ashafa","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-04-07T15:26:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":237,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/77332\/need-help-with-the-formula-to-update-status-based-on-changes-to-a-field","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77332\/need-help-with-the-formula-to-update-status-based-on-changes-to-a-field","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/HISD3OSDG0VJ\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T21:15:41+00:00","dateAnswered":"2021-03-16T16:01:10+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":78507,"type":"question","name":"Contains in formula","excerpt":"Hello all! I am trying to use multiple \"contains\" functions in a formula. I would like to search multiple columns(@row) with several strings contained in them for certain \"keywords\" and return a value in another column (@row) based on that search. So, for example I want to search the first row of the first three columns…","categoryID":322,"dateInserted":"2021-04-19T13:22:23+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T21:03:59+00:00","insertUserID":134476,"insertUser":{"userID":134476,"name":"Bob Kernan","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Kernan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ptneqR6hvT4!25lePybC4zo!zaSyDmYv93c","dateLastActive":"2022-08-23T21:56:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134476,"lastUser":{"userID":134476,"name":"Bob Kernan","url":"https:\/\/community.smartsheet.com\/profile\/Bob%20Kernan","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ptneqR6hvT4!25lePybC4zo!zaSyDmYv93c","dateLastActive":"2022-08-23T21:56:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":112,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/78507\/contains-in-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78507\/contains-in-formula","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EA9HZZHC2JIU\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T21:03:24+00:00","dateAnswered":"2021-04-19T14:13:15+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78534,"type":"question","name":"Formula to Check a box at different indention levels","excerpt":"Hey there, Trying to figure out a formula to automatically check the boxes based on how many times a task has been indented, like this: I know I can set the Activity column to: =IF(COUNT(CHILDREN([Task Name]@row)) = 0, 1), but I don't know how to do the others. Any help would be appreciated!","categoryID":322,"dateInserted":"2021-04-19T20:09:16+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T20:18:30+00:00","insertUserID":134883,"insertUser":{"userID":134883,"name":"Ryan Levitz","url":"https:\/\/community.smartsheet.com\/profile\/Ryan%20Levitz","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!w8jQ_6i5hSQ!!aMFH1gMJSqK","dateLastActive":"2023-04-19T16:14:22+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":139,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/78534\/formula-to-check-a-box-at-different-indention-levels","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78534\/formula-to-check-a-box-at-different-indention-levels","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/EAHHJ3RI56ED\/screen-shot-2021-04-19-at-2-51-08-pm.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screen Shot 2021-04-19 at 2.51.08 PM.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T20:18:33+00:00","dateAnswered":"2021-04-19T20:15:17+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]},{"discussionID":77890,"type":"question","name":"If parent card is dragged to later row in roadmap, then children cell value changes.","excerpt":"Hi All. This is very similar to the thread that @Paul Newcome answered on children rows populating \/ changing a parent row here: https:\/\/community.smartsheet.com\/discussion\/13326\/formula-to-update-status-on-a-parent-row-based-on-any-child-row However, my situation is opposite. My ask: if I drag a parent card to a further…","categoryID":322,"dateInserted":"2021-03-31T20:31:40+00:00","dateUpdated":"2021-03-31T20:32:57+00:00","dateLastComment":"2021-04-19T20:17:38+00:00","insertUserID":134409,"insertUser":{"userID":134409,"name":"Colton Long","url":"https:\/\/community.smartsheet.com\/profile\/Colton%20Long","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-04-19T20:16:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":134409,"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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":62,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/77890\/if-parent-card-is-dragged-to-later-row-in-roadmap-then-children-cell-value-changes","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/77890\/if-parent-card-is-dragged-to-later-row-in-roadmap-then-children-cell-value-changes","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T20:16:51+00:00","dateAnswered":"2021-04-08T23:38:47+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78529,"type":"question","name":"Need help with an IF statement \/ Add 50% to formula result","excerpt":"Hello, I've wrecked my brain trying to figure out the formula just to add 50% to the returned value. I got close but it seems to be off by a few dollars. I also will need to use this in an IF formula. If Express Check = N then =SUM[Column2]@row - 5000) * 0.64 + 11002.5 total is 11017.86 My formula =SUM([Column2]@row -…","categoryID":322,"dateInserted":"2021-04-19T18:50:26+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T19:57:02+00:00","insertUserID":131617,"insertUser":{"userID":131617,"name":"Rosa Crisostomo","url":"https:\/\/community.smartsheet.com\/profile\/Rosa%20Crisostomo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!XH9TXyCyDfQ!-w_Rt3yEHn4!JQ-10XBpdLz","dateLastActive":"2021-05-14T18:49:36+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":94,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/78529\/need-help-with-an-if-statement-add-50-to-formula-result","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78529\/need-help-with-an-if-statement-add-50-to-formula-result","format":"Rich","groupID":null,"statusID":2,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9ZDEE9ESX0BM\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2021-04-19T19:07:15+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78524,"type":"question","name":"Can you help me figure out what is causing the Invalid Operation Error for this formula?","excerpt":"=IF(AND(Finish@row < TODAY() >= Finish@row, [% Complete]@row < 1), \"Red\", IF(AND(Finish@row < TODAY(-3), [% Complete]@row < 0.99), \"Yellow\", \"Green\"))","categoryID":322,"dateInserted":"2021-04-19T17:41:33+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T19:37:23+00:00","insertUserID":135164,"insertUser":{"userID":135164,"name":"Kate Hazelton","url":"https:\/\/community.smartsheet.com\/profile\/Kate%20Hazelton","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-04-21T21:00:44+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":42,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/78524\/can-you-help-me-figure-out-what-is-causing-the-invalid-operation-error-for-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78524\/can-you-help-me-figure-out-what-is-causing-the-invalid-operation-error-for-this-formula","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T19:14:30+00:00","dateAnswered":"2021-04-19T18:11:31+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78521,"type":"question","name":"Pull Last Entry from One Sheet to Another Based on Criteria","excerpt":"I'm trying to figure out a formula to add the last or latest \"Status Notes\" entry from our Work Request Monthly Update sheet to the correct\/matching Work Request's \"Status Notes\" on our Master Work Log Sheet. Both sheet's Primary Column's are named Work Request Number and have mirroring numbers. The Monthly Update sheet…","categoryID":322,"dateInserted":"2021-04-19T17:04:40+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T19:31:05+00:00","insertUserID":127796,"insertUser":{"userID":127796,"name":"Jonna Critchley","url":"https:\/\/community.smartsheet.com\/profile\/Jonna%20Critchley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-01T15:15:38+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":19,"countViews":167,"score":null,"hot":19,"url":"https:\/\/community.smartsheet.com\/discussion\/78521\/pull-last-entry-from-one-sheet-to-another-based-on-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78521\/pull-last-entry-from-one-sheet-to-another-based-on-criteria","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T19:19:12+00:00","dateAnswered":"2021-04-19T18:58:39+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":78530,"type":"question","name":"Is it possible to change the wording on the \"Open Update Form\" button?","excerpt":"Is it possible to change wording on the \"Open Update Form\" button?","categoryID":322,"dateInserted":"2021-04-19T18:57:33+00:00","dateUpdated":null,"dateLastComment":"2021-04-19T19:04:04+00:00","insertUserID":127681,"insertUser":{"userID":127681,"name":"Greg Arnson","url":"https:\/\/community.smartsheet.com\/profile\/Greg%20Arnson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T19:00:26+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":103,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/78530\/is-it-possible-to-change-the-wording-on-the-open-update-form-button","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/78530\/is-it-possible-to-change-the-wording-on-the-open-update-form-button","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2021-04-19T19:25:30+00:00","dateAnswered":"2021-04-19T19:04:04+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources