公式

TODAY(), \"Green\") 3) if %complete is full and finish date…","categoryID":319,"dateInserted":"2017-05-12T15:12:37+00:00","dateUpdated":null,"dateLastComment":"2018-04-23T15:12:40+00:00","insertUserID":14976,"insertUser":{"userID":14976,"name":"Jawanza Hadley","url":"https:\/\/community.smartsheet.com\/profile\/Jawanza%20Hadley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-11\/2d\/08\/n2d083c99ee208833d8ee158468f6baf8.jpg","dateLastActive":"2019-08-28T11:20:15+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":45711,"lastUser":{"userID":45711,"name":"Jeff Ford","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Ford","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-11-06T14:28:53+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":true,"sink":false,"countComments":10,"countViews":67,"score":null,"hot":10,"url":"https:\/\/community.smartsheet.com\/discussion\/9256\/help-with-nested-if-formulat","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/9256\/help-with-nested-if-formulat","format":"Html","groupID":null,"statusID":0,"attributes":{},"bookmarked":false,"unread":false,"category":{"categoryID":319,"name":"Archived 2017 Posts","url":"https:\/\/community.smartsheet.com\/categories\/c2017","allowedDiscussionTypes":["discussion","question"]},"tags":[{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"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":23666,"type":"discussion","name":"VLOOKUP and Conditional Formatting","excerpt":"I updated our smartsheet team schedule to grab dates from my department's master schedule. However, the conditional formatting we set up that visually shows us when a project is nearing or past its due date no longer works on the VLOOKUP cells Is there something I'm doing wrong? Or is this a limitation of the software? Has…","categoryID":322,"dateInserted":"2018-04-16T09:56:54+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T14:53:50+00:00","insertUserID":44921,"insertUser":{"userID":44921,"name":"joconnor","url":"https:\/\/community.smartsheet.com\/profile\/joconnor","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2018-04-23T14:11:15+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":11072,"lastUser":{"userID":11072,"name":"Mike Wilday","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Wilday","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/518\/n0UL39EAWX965.jpg","dateLastActive":"2023-01-03T23:02:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":119,"score":null,"hot":7,"url":"https:\/\/community.smartsheet.com\/discussion\/23666\/vlookup-and-conditional-formatting","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23666\/vlookup-and-conditional-formatting","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":213,"urlcode":"Conditional Formatting","name":"Conditional Formatting"},{"tagID":228,"urlcode":"Project Management","name":"Project Management"},{"tagID":231,"urlcode":"Cell linking","name":"Cell linking"},{"tagID":241,"urlcode":"Calendar","name":"Calendar View"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":270,"urlcode":"Technology","name":"Technology"},{"tagID":299,"urlcode":"Card View","name":"Card View"}],"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":24031,"type":"discussion","name":"% as Negative","excerpt":"How do you structure your formula calculate negative if it is over 100% For example, I am at 144% which is over budget Current Formula =AC1 \/ [Project Budget]1 Results 144%, I would like it to calculate -44% once I am over budget. ","categoryID":322,"dateInserted":"2018-04-20T17:53:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T11:36:38+00:00","insertUserID":41966,"insertUser":{"userID":41966,"name":"Tony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Barthelemy","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2021-02-21T20:22:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":41966,"lastUser":{"userID":41966,"name":"Tony Barthelemy","url":"https:\/\/community.smartsheet.com\/profile\/Tony%20Barthelemy","photoUrl":"https:\/\/lh3.googleusercontent.com\/-XdUIqdMkCWA\/AAAAAAAAAAI\/AAAAAAAAAAA\/4252rscbv5M\/photo.jpg","dateLastActive":"2021-02-21T20:22:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":107,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/24031\/as-negative","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24031\/as-negative","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":23996,"type":"discussion","name":"Nesting SUBSTITUTE functions","excerpt":"Hello All, I need help!!! I am trying to write a formula that replaces the letter \"a\" with the number \"1\", \"b\" with \"2\", \"c\" with \"3\", etc.... The individual formula of (ex.) =SUBSTITUTE([Column3]1, \"a\", \"1\") is easy enough, but for whatever reason I can't seem to figure out how to nest it with the rest of the letters and…","categoryID":322,"dateInserted":"2018-04-20T10:26:43+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-23T08:04:28+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":"2022-12-30T17:37:42+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":"2022-12-30T17:37:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":232,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/23996\/nesting-substitute-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23996\/nesting-substitute-functions","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":209,"urlcode":"Media & Entertainment","name":"Media & Entertainment"},{"tagID":216,"urlcode":"Non-profit","name":"Non-profit"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":263,"urlcode":"Government","name":"Government"},{"tagID":281,"urlcode":"Support","name":"Support"}],"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":23836,"type":"discussion","name":"Counting Unique Values based on a date criteria","excerpt":"I am using this formula to count for unique values within a range of cell. I want to be able to use a criteria, a date for the range of cell, what should the formula look like? =SUM((IF(FREQUENCY(MATCH(B2:B16,B2:B16,0),MATCH(B2:B16,B2:B16,0))>0,1)))","categoryID":322,"dateInserted":"2018-04-18T09:55:06+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-22T21:27:56+00:00","insertUserID":42091,"insertUser":{"userID":42091,"name":"ODT","url":"https:\/\/community.smartsheet.com\/profile\/ODT","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/58\/75\/n5875e0f99afbea49ab1b2f57a7bf2f6d.jpg","dateLastActive":"2019-05-09T23:43:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":42091,"lastUser":{"userID":42091,"name":"ODT","url":"https:\/\/community.smartsheet.com\/profile\/ODT","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2018-03\/58\/75\/n5875e0f99afbea49ab1b2f57a7bf2f6d.jpg","dateLastActive":"2019-05-09T23:43:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":1958,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/23836\/counting-unique-values-based-on-a-date-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/23836\/counting-unique-values-based-on-a-date-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"}],"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":24056,"type":"discussion","name":"Help on formula: How to copy adjacent text using a specific text from a different column","excerpt":"Hi all, Please need help on formula. The example image below is a project with sub tasks and I wanted to create a formula in the Progress column highlighted in yellow (this is the main header). The condition is: if the status of the row is \"Active\", then show me what is its current Progress. Based on the screenshot below,…","categoryID":322,"dateInserted":"2018-04-22T06:39:14+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-22T11:39:52+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":42606,"lastUser":{"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":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":55,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/24056\/help-on-formula-how-to-copy-adjacent-text-using-a-specific-text-from-a-different-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24056\/help-on-formula-how-to-copy-adjacent-text-using-a-specific-text-from-a-different-column","format":"Html","groupID":null,"statusID":0,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_attachment\/files\/2018-04\/6d\/d8\/6dd8c895a6931c96d2bddb0267b79074.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Progress.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"},{"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":24041,"type":"discussion","name":"Adding %Complete back to smartsheet","excerpt":"I accidentally deleted the %complete column in my smartsheet. How do I insert it back into the project? I don't see a quick way of doing this. ","categoryID":322,"dateInserted":"2018-04-20T18:32:34+00:00","dateUpdated":"2019-12-09T08:04:32+00:00","dateLastComment":"2018-04-21T12:36:37+00:00","insertUserID":45571,"insertUser":{"userID":45571,"name":"andrew.r.bridges45571","url":"https:\/\/community.smartsheet.com\/profile\/andrew.r.bridges45571","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2019-06-21T14:42:17+00:00","banned":0,"punished":0,"private":false,"label":""},"updateUserID":null,"lastUserID":19476,"lastUser":{"userID":19476,"name":"Alison","url":"https:\/\/community.smartsheet.com\/profile\/Alison","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2017-12\/6a\/70\/n6a70dc495c17afa3a0a194676d7ebfd7.png","dateLastActive":"2020-04-09T19:24:18+00:00","banned":0,"punished":0,"private":false,"label":"Employee"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":403,"score":null,"hot":2,"url":"https:\/\/community.smartsheet.com\/discussion\/24041\/adding-complete-back-to-smartsheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/24041\/adding-complete-back-to-smartsheet","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":231,"urlcode":"Cell linking","name":"Cell linking"},{"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}">