Dynamics metrics across new sheets
Goal: create metrics for a dashboard that automatically update when new project plans are created in the same workspace.
For example: KPI=Number of late deliveries across all projects at a client
I can create a metrics sheet and create a formula to pull data across sheets, but each time a new project is started, I would need to manually edit all the formulas to add the new reference to the sheet. I don't think there is a way to create sheet references across all plans in a folder or workspace, let's say.
I also thought about using reports and/or sheet summaries, which gets me close. I can use sheet summaries to get total late deliveries for each project, and run a sheet summary report pointed to a workspace (this auto updates the report for new project plans within) but it still reports them by project and there is no way to sum them up in the report. Finally, there is no way to link to the report to create metrics off of it.
Any ideas? Am I missing something. I am trying to future proof a system where I don't need to manually update formulas every time there is a new project that needs to be included in a dashboard.