Report Doesn't update status from sheets
I have a report pulling status from different project sheets and notice that the Status (R, Y, G) does not automatically update in the report.
I have to open and close each underlying sheet for the status in the report to be updated. My report is pulling information from 100+ sheets. The status column in each sheets uses same formula (Red if planned date is in the past, yellow if planned date is within the next 7 days) and works within the report without any problem
The formula in Status column is: =IF(Level@row = "", IF(AND([% Complete]@row < 1, [Planned Finish]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [Planned Finish]@row < TODAY(7)), "Yellow", IF(AND([% Complete]@row = 1, [Planned Finish]@row <> ""), "Green", "Gray"))), IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Gray")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Gray")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Gray")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Yellow")), "Gray", "")))))
What am I doing wrong?
Best Answers
-
Paul Newcome ✭✭✭✭✭✭It sounds like there may be an issue with the TODAY function being used. The TODAY function will not update to today's date until the sheet is activated. There are a number of ways to "activate" a sheet which includes opening the sheet itself.
You can force a "today refresh" by inserting a date column on each sheet then creating a Record A Date automation triggered to run daily to record the date in the new date column with a condition of when [Column Of Choice] is blank or [Column Of Choice] is not blank.
Additionally (not an impact on the report but something I noticed), your formula can be condensed quite a bit to allow for more efficient back-end processing.
Original:
=如果(Level@row = " ",如果(((%完成)@row < 1,Planned Finish]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [Planned Finish]@row < TODAY(7)), "Yellow", IF(AND([% Complete]@row = 1, [Planned Finish]@row <> ""), "Green", "Gray"))), IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Gray")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Gray")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Gray")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Yellow")), "Gray", "")))))
Condensed:
=IF(Level@row <> "", IF([% Complete]@row = 1, "Green", IF([Planned Finish]@row < TODAY(), "Red", IF([Planned Finish]@row < TODAY(7), "Yellow", "Gray"))), IF(COUNTIF(CHILDREN(), "Green")>= MAX(COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Gray")), "Green", IF(COUNTIF(CHILDREN(), "Yellow")>= MAX(COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray")), "Yellow", IF(COUNTIF(CHILDREN(), "Red")>= MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Gray")), "Red", "Gray"))))
-
Paul Newcome ✭✭✭✭✭✭
Answers
-
Paul Newcome ✭✭✭✭✭✭
It sounds like there may be an issue with the TODAY function being used. The TODAY function will not update to today's date until the sheet is activated. There are a number of ways to "activate" a sheet which includes opening the sheet itself.
You can force a "today refresh" by inserting a date column on each sheet then creating a Record A Date automation triggered to run daily to record the date in the new date column with a condition of when [Column Of Choice] is blank or [Column Of Choice] is not blank.
Additionally (not an impact on the report but something I noticed), your formula can be condensed quite a bit to allow for more efficient back-end processing.
Original:
=如果(Level@row = " ",如果(((%完成)@row < 1,Planned Finish]@row < TODAY()), "Red", IF(AND([% Complete]@row < 1, [Planned Finish]@row < TODAY(7)), "Yellow", IF(AND([% Complete]@row = 1, [Planned Finish]@row <> ""), "Green", "Gray"))), IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Gray")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Gray")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Gray")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") > COUNTIF(CHILDREN(), "Yellow")), "Gray", "")))))
Condensed:
=IF(Level@row <> "", IF([% Complete]@row = 1, "Green", IF([Planned Finish]@row < TODAY(), "Red", IF([Planned Finish]@row < TODAY(7), "Yellow", "Gray"))), IF(COUNTIF(CHILDREN(), "Green")>= MAX(COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Gray")), "Green", IF(COUNTIF(CHILDREN(), "Yellow")>= MAX(COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray")), "Yellow", IF(COUNTIF(CHILDREN(), "Red")>= MAX(COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Gray")), "Red", "Gray"))))
-
[email protected] ✭✭✭✭
@Paul Newcome- Thank you for your help. and thanks for the condensed formulas that came in as a bonus for me!
-
Paul Newcome ✭✭✭✭✭✭
Categories
<\/p>
=VALUE(IFERROR(JOIN(DISTINCT(COLLECT([Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, [Customer PO Amount (USD)]@row:[Customer PO Amount (Local Currency)]@row, <>\"\"))), \" \"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109460,"type":"question","name":"IF \/ OR formula then Check a Box","excerpt":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1) OR([Study Status]@row = \"In Start-Up\", 1))","snippet":"I need a formula that IF a specific column has either 1 of 2 specific choices, then a BOX in another column is checked: =IF(([Study Status]@row = \"Active\/ Open to Accrual\", 1)…","categoryID":321,"dateInserted":"2023-08-25T13:30:39+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T16:21:27+00:00","insertUserID":9250,"insertUser":{"userID":9250,"name":"Susan Swisher","url":"https:\/\/community.smartsheet.com\/profile\/Susan%20Swisher","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T16:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":45,"score":null,"hot":3385956126,"url":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109460\/if-or-formula-then-check-a-box","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109460,"commentID":392616,"name":"Re: IF \/ OR formula then Check a Box","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392616#Comment_392616","dateInserted":"2023-08-25T16:21:27+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T15:13:03+00:00","dateAnswered":"2023-08-25T14:14:39+00:00","acceptedAnswers":[{"commentID":392575,"body":"
Give this a try:<\/p>
=IF(OR([Study Status]@row = \"Active\/Open to Accrual\", [Study Status]@row = \"In Start-Up\"), 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109457,"type":"question","name":"Conditional Formatting (modified date not in the last 3 weeks)","excerpt":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated (last 3 weeks) should be highlighted in yellow. Is this spmething that can be done directly within conditional formatting or should I first create a…","snippet":"I would like to use Conditional Formatting (highlight the entire row yellow) if the modified date is not within the last 3 weeks. So any row that has not been recently updated…","categoryID":321,"dateInserted":"2023-08-25T12:33:14+00:00","dateUpdated":null,"dateLastComment":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161267,"lastUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3385935251,"url":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109457\/conditional-formatting-modified-date-not-in-the-last-3-weeks","format":"Rich","tagIDs":[437],"lastPost":{"discussionID":109457,"commentID":392553,"name":"Re: Conditional Formatting (modified date not in the last 3 weeks)","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392553#Comment_392553","dateInserted":"2023-08-25T12:40:57+00:00","insertUserID":161267,"insertUser":{"userID":161267,"name":"Jef Snyders","title":"Jef Snyders","url":"https:\/\/community.smartsheet.com\/profile\/Jef%20Snyders","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/4HJAEW33KBD0\/nXEKEZE5EQEV4.jpg","dateLastActive":"2023-08-25T13:32:31+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T12:40:09+00:00","dateAnswered":"2023-08-25T12:37:47+00:00","acceptedAnswers":[{"commentID":392551,"body":"
There is no direct way to do this. Adding a checkbox helper column with something similar to this and then formatting based on the checkbox is the simplest way. You can even hide the extra column, as there is no reason for it to be visible.<\/p>
=IF([Modified Date]@row < TODAY(-21), 1, 0)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[{"tagID":437,"urlcode":"conditional-formatting","name":"Conditional Formatting"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&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":4991,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
