How to flag a total in a report that exceeds a limit?

Mike. F

I am trying to figure out a clever work around to the limitation of referencing reports in formulas. We have numerous independent schedules.... I compile certain tasks from each schedule, for example "drawing releases", into a single report. I want to be able to monitor the grand total from the report relative to a limit – if the limit is exceeded I would highlight on a dashboard.

The report has the exact data I need and a sheet has all the "limits".... if i could get the report to a sheet I could make it work.

Datamesh almost works... if it would delete rows. Pivot almost works if I could lock rows. I could brute force it by linking cells but that is not sustainable.

Fingers crossed…Thanks!

Lucas Rayala

Hi @Mike F. , maybe your complicating this too much? Create a sheet to put a table that tracks all your tasks. For each task you want to track, compile one formula into a single cell that references each schedule, for instance:

=COUNTIFS({schedule 1 task column}, "drawing released") + COUNTIFS({schedule 2 task column}, "drawing released") + COUNTIFS({schedule 3 task column}, "drawing released")

You can pile the countifs into one cell and add them together to sum the task counts for all your sheets. Repeat this for all your other tasks and you could make a little table for a dashboard.

Let me know if this helps, and please flag this post if I answered your question! Good luck!