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
-
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:
=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", "")))))
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"))))
-
Happy to help. 👍️
Answers
-
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:
=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", "")))))
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 - Thank you for your help. and thanks for the condensed formulas that came in as a bonus for me!
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives