Report Doesn't update status from sheets

tariq.shaikh@signspecialists.com

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?

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:

=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

Happy to help. 👍️

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:

=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"))))

tariq.shaikh@signspecialists.com

@Paul Newcome - Thank you for your help. and thanks for the condensed formulas that came in as a bonus for me!

Paul Newcome

Happy to help. 👍️