Automatic RollUp of data from multiple projects

Hello everyone ! 

I could use some help with some dashboard 101. 

I've been trying to find a way to roll up data for a while without success. Let me explain the situation:

I've created a "project folder template" (including sheets, report and a dashboard) that I can easily copy for every new project I'm creating (please refer to ProjectStructure screenshot below)

For every sheet that I have, I've created summary metrics (see summarypersheet below) 

I've also created some Sheet Summary data, all of them functions refering to some data in my sheets. (See sheet summary screenshot below)

From that Sheet summary, I can easily create Summary Reports from these fields like the summaryreport screeshot below. 

Now my problem is that I am unable to automatically roll up data from new project into metrics for a dahsboard. 

I can create these kind of metrics with formulas that could look like this: =COUNTIF({BI COVEO - RYG}, [...]9) + COUNTIF({ERP@COVEO - RYG}, [...]9) to create these kind of widgets:

But that forces me to modify the data field formulas everytime I'm creating a new projects. 

I'm looking for a way to automate that process so that everytime I'm creating a new projects from my templates, the metrics will update automatically (rolling up to a portfolio dashboard) 

Since I can't create metrics from a summary report and that I can't seem to find a way to create a formula in a sheet that will allow me to look in a "folder" or a "workspace", I'm at a lost. 

If you have a solution, I would appreciate that. 

Thanks ! 

Christian

ProjectStructure.png

SummaryperSheet.png

SheetSummary.png

SummaryReport.png

PieChart.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!