Dynamics metrics across new sheets

BHannah

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.

Paul Newcome

I use a series of hidden columns for this. Each column pulls the pertinent data I want to use for metrics from the main part of the sheet.

I then use a "Rollup" sheet where each project has it's own row. You can cell link multiple cells at a time so long as the range you highlight and the range you select to link are the same dimensions.

I then run my metrics off of my "Rollup" sheet. There is still a little manual work for each new project, but it is a matter of taking a minute or two to create some cell links instead of having to recreate formulas with new ranges pointing at the new sheet.

Paul Newcome

I use a series of hidden columns for this. Each column pulls the pertinent data I want to use for metrics from the main part of the sheet.

I then use a "Rollup" sheet where each project has it's own row. You can cell link multiple cells at a time so long as the range you highlight and the range you select to link are the same dimensions.

I then run my metrics off of my "Rollup" sheet. There is still a little manual work for each new project, but it is a matter of taking a minute or two to create some cell links instead of having to recreate formulas with new ranges pointing at the new sheet.

BHannah

Thanks Paul, seems like a area for improvement for aggregation across projects!

But we can use the workarounds in the meantime.

Paul Newcome

Happy to help! 👍️