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?

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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!