Metrics NOT updating periodically as expected

Christine Cao

Not sure if anyone is experiencing this. I've created a METRICS sheet for every project that sums up counts for Task Health in order to present on Dashboard through Graphs. I'm noticing that theses metrics are NOT automatically updated unless I refresh the page a dozen times OR re-write the exact same formula again. But then there's no guarantee that will fix going forward. Just happen to notice as I spot check against project plans. Odd thing is other metric formula on the SAME sheet is auto updating as expected. Here's the Formula(s) for these three metric data that's having issues. Is there some tip to resolve or write in a way that's better? All these uses external reference sheet which is the project plan.

=COUNTIFS({Past Due}, 1, {Level}, <>1, {Level}, <>2)

=COUNTIFS({Project Plan Status}, "Completed", {Level}, <>1, {Level}, <>2)

=COUNTIFS({Project Plan Status}, "Not Started", {Level}, <>1, {Level}, <>2)

Matt Johnson

Hi @Christine Cao

No problem. Add a date column (and probably hide it). Then go the Automations button on the upper left of the sheet. From there, create new from scratch. I called my automation the same as the column I added; "TODAY (sheet refresh)". Below is how my settings are for the automation. I hope that does the trick. -Matt

Matt Johnson

Hi @Christine Cao

Have you heard about the TODAY function needing to be refreshed daily? If so, and that's not the case, then disregard below.

One reason this could be happening is if the columns on source sheet are automated based on today's date somehow. For a general example, IF the estimated finish date is in the past, or less than today, AND the status is not complete, THEN mark it "Past Due". If that sheet is not opened, or refreshed everyday, then the formula might not work until it is. The fix for this is to add a helper column called something like "Auto-TODAY" or "TODAY trigger" and then tie an automation to that so everyday it updates with Today's date at like 1am or something. That ensures that everything is refreshed each day regardless if its ever opened.

That might be not be the fix but thought I'd throw it out there just in case.

I hope that helps.

Matt

Christine Cao

@Matt Johnson Thank you for your input Matt. I am not aware of this TODAY Function having to be refreshed. I'll have to look into this more. The TODAY function is included in the formula to calculate the "RYG on the source sheet (project plan) which is what this metric is counting. So you may be correct in your assessment. How do I create this "helper", Automation so that the source sheet is updated with TODAY's date always? Suggestion on where I can find this guidance?

Matt Johnson

Hi @Christine Cao

No problem. Add a date column (and probably hide it). Then go the Automations button on the upper left of the sheet. From there, create new from scratch. I called my automation the same as the column I added; "TODAY (sheet refresh)". Below is how my settings are for the automation. I hope that does the trick. -Matt

Christine Cao

@Matt Johnson I did this as you suggested and will monitor to see if this fixes things. Thank you again for your suggestion. Learned something new that's super helpful. - Christine