Formulas

1; IF(due_date15 - planned_date15 > 0; \"Red\"; IF(due_date15 - planned_date15 > -3; \"Yellow\"; \"Green\"))) But now I would like to include a blue ball when the \"finish\" checkbox is checked. I tried some alternatives,…","categoryID":322,"dateInserted":"2018-05-29T18:39:53+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-30T01:46:32+00:00","insertUserID":44736,"insertUser":{"userID":44736,"name":"vanzin","url":"https:\/\/community.smartsheet.com\/profile\/vanzin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/8f\/64\/n8f6494f8ce88c7dd1e852dc637bf22b7.png","dateLastActive":"2018-05-29T18:38:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":23666,"lastUser":{"userID":23666,"name":"Chris McKay","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20McKay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-09\/d5\/d2\/nd5d2693be28ccbd24f978b00c5d1db1d.JPG","dateLastActive":"2018-08-14T22:59:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":63,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/26071\/rygb-x-checkbox-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26071\/rygb-x-checkbox-question","format":"Html","groupID":null,"statusID":0,"attributes":{},"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":25981,"type":"discussion","name":"How to reference the ","excerpt":"Is there a way to reference the cell above? example: =IF(logical_expression, value_if_true, ) The simple way is to reference using [COLUMN_NAME]2 (the number being the current row number minus one). But this is broken anytime a row is deleted, which happens frequently on our sheet. I have tried adding an…","categoryID":322,"dateInserted":"2018-05-28T21:07:57+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-29T23:02:07+00:00","insertUserID":49231,"insertUser":{"userID":49231,"name":"BP97","url":"https:\/\/community.smartsheet.com\/profile\/BP97","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2021-10-28T18:56:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":23666,"lastUser":{"userID":23666,"name":"Chris McKay","url":"https:\/\/community.smartsheet.com\/profile\/Chris%20McKay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-09\/d5\/d2\/nd5d2693be28ccbd24f978b00c5d1db1d.JPG","dateLastActive":"2018-08-14T22:59:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":10737,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/25981\/how-to-reference-the","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/25981\/how-to-reference-the","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":211,"urlcode":"Manufacturing","name":"Manufacturing"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":507,"urlcode":"enhancement-request","name":"Enhancement Request"}],"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":19496,"type":"discussion","name":"Invalid data type result when joining a complex collection","excerpt":"I'm receiving an \"#INVALID DATA TYPE\" result with the following forumla: =JOIN(COLLECT({LocationStrings}, {LocationStrings}, NOT(ISBLANK(@cell)), {PersonNames}, @cell = $[Person contact list]29, {ProjectStatus}, OR(@cell = $Status$5, @cell = $Status$6), {DeliveryDate}, AND(MONTH(@cell) = MONTH(TODAY()), YEAR(@cell) =…","categoryID":322,"dateInserted":"2018-02-13T23:44:06+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-29T19:01:05+00:00","insertUserID":26786,"insertUser":{"userID":26786,"name":"csprocket777","url":"https:\/\/community.smartsheet.com\/profile\/csprocket777","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-02\/7c\/73\/n7c736f067ac3a08af384952b7ebf6ea2.jpeg","dateLastActive":"2018-02-13T23:40:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":49461,"lastUser":{"userID":49461,"name":"sdill08","url":"https:\/\/community.smartsheet.com\/profile\/sdill08","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-05-29T18:56:50+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":164,"score":null,"hot":5,"url":"https:\/\/community.smartsheet.com\/discussion\/19496\/invalid-data-type-result-when-joining-a-complex-collection","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/19496\/invalid-data-type-result-when-joining-a-complex-collection","format":"Html","groupID":null,"statusID":0,"attributes":{},"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":26041,"type":"discussion","name":"Display last value in column","excerpt":"Hello Community! My question is quite simple. (I hope). How can I display the last value of a column? I tried to use the formula = INDEX, but I could not read the last number automatically. Thanks! ","categoryID":322,"dateInserted":"2018-05-29T15:29:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-29T16:17:30+00:00","insertUserID":44736,"insertUser":{"userID":44736,"name":"vanzin","url":"https:\/\/community.smartsheet.com\/profile\/vanzin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/8f\/64\/n8f6494f8ce88c7dd1e852dc637bf22b7.png","dateLastActive":"2018-05-29T18:38:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":44736,"lastUser":{"userID":44736,"name":"vanzin","url":"https:\/\/community.smartsheet.com\/profile\/vanzin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/8f\/64\/n8f6494f8ce88c7dd1e852dc637bf22b7.png","dateLastActive":"2018-05-29T18:38:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":1158,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/26041\/display-last-value-in-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/26041\/display-last-value-in-column","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-05\/43\/bd\/43bdaa1cb62455006ecae9bf4454746a.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"example.png"},"attributes":{},"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":24951,"type":"discussion","name":"Help on formula: Countifs with multiple columns and criteria","excerpt":"Hi, So I've been working on a formula but couldn't get it right. I wanted to count the number of my projects that are Active (on-time) and Active (delayed). I get the on-time count correctly but for the delayed ones I always get a plus 1 in the results. Here's the formula I use (btw the stages represents that a project is…","categoryID":322,"dateInserted":"2018-05-09T07:32:38+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-29T11:45:29+00:00","insertUserID":42606,"insertUser":{"userID":42606,"name":"Grace","url":"https:\/\/community.smartsheet.com\/profile\/Grace","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-08-14T08:25:04+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":195,"lastUser":{"userID":195,"name":"Shaine Greenwood","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Shaine%20Greenwood","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/198\/nBSBY6VR571R7.jpg","dateLastActive":"2021-08-30T16:03:52+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":182,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/24951\/help-on-formula-countifs-with-multiple-columns-and-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24951\/help-on-formula-countifs-with-multiple-columns-and-criteria","format":"Html","groupID":null,"statusID":0,"attributes":{},"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"},{"tagID":285,"urlcode":"Solution Center","name":"Solution Center"}],"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":25946,"type":"discussion","name":"count if check box is not checked (2) and requested date is within a date range","excerpt":"I am trying to get a count of each row on my sheet that meets the following criteria. Children of ([Requested Date]17) (which is date format column) is between 1\/1\/18 - 1\/31\/18 and Children of (Canceled17) (which is check box format) is not checked (0) and Children of ([Not Owner]17) (which is a check box format) is not…","categoryID":322,"dateInserted":"2018-05-25T18:08:51+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-29T09:12:13+00:00","insertUserID":34191,"insertUser":{"userID":34191,"name":"Johnnie LaCaze","url":"https:\/\/community.smartsheet.com\/profile\/Johnnie%20LaCaze","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hG_ZIf2kJX4!nyDY_y27AGY!dq1DJJdZpds","dateLastActive":"2022-07-01T15:52:58+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-07T21:40:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":241,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/25946\/count-if-check-box-is-not-checked-2-and-requested-date-is-within-a-date-range","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/25946\/count-if-check-box-is-not-checked-2-and-requested-date-is-within-a-date-range","format":"Html","groupID":null,"statusID":0,"attributes":{},"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":25891,"type":"discussion","name":"Nested IF and Forms","excerpt":"Good morning. I created a meeting request form for business units to fill out so we can capture information we need about an upcoming conference. I'm trying to rig the way the information is captured on the sheet and am having a little trouble with the Nested IF. I have columns for each meeting date and a drop-down option…","categoryID":322,"dateInserted":"2018-05-24T09:13:21+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-05-28T20:24:16+00:00","insertUserID":43696,"insertUser":{"userID":43696,"name":"karen_berard","url":"https:\/\/community.smartsheet.com\/profile\/karen_berard","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-05\/5d\/1a\/n5d1a89f3cd2ac677dbfd4e93ff1a8cc9.PNG","dateLastActive":"2022-11-18T18:09:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":24581,"lastUser":{"userID":24581,"name":"Andrew Stewart","url":"https:\/\/community.smartsheet.com\/profile\/Andrew%20Stewart","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pZdd6VRKs7o!3eNef4Qckbo!i7PuC5gsNf3","dateLastActive":"2020-08-04T02:22:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":92,"score":null,"hot":1,"url":"https:\/\/community.smartsheet.com\/discussion\/25891\/nested-if-and-forms","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/25891\/nested-if-and-forms","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-05\/2b\/51\/2b511bf7521d67cfbf6ace2ee25c3c2a.PNG","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SmartSheet Community.PNG"},"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">