looking to do the exact same thing... need to summarize data from multiple sheet summaries, and would like to input that into a chart for a dashboard to track progress across multiple projects. Now realizing same issue as yours - can't sum this info on a report, can't cell link the report into a metric sheet, and very labour intensive to write all the fx for this manually.

I need to do the same

I work with hundreds of sheets that have the same column names. Each row is a different transaction. I use reports to combine sheets or sheets in workspaces. Then I connect excel to the smartsheet report using the data connector (enterprise account or greater required). I use the connections as the source data for a pivot table. I use pivot tables to sum, average, count, etc. If you have access to other tools (tableau, qlik, power BI) you can use them too.

I think the Pivot App might work too and is an in-app solution. I suspect it is not as robust as other tools mentioned above, at least not yet.

If you end up using Smartsheet with multiple integrations I would try using MS-Teams to keep things organized.

Hope this helps,

Paul,

I have tried to understand your approach above to no avail. I think this will accomplish my problem, but am getting lost in your text. Let me explain what I want to do, along with how my files are structured. If what you wrote is a solution, then I will just need a little clearer explanation. I have project files that I've created in CC. In each one of those, I have some summary fields which compare specific dates in the sheet and tell me the number of months between the two dates. I have created a report and graph to get this data onto a dashboard. There are four numbers from each project that I graphed. I would now like to know what the average is of the numbers, and be able to get these averages onto the dashboard. Does your solution accomplish this?

For the solution you described, did you create those columns in the roll-up sheet or in each individual projects files where the Summary Fields are? I tried in the roll-up sheet, but it can just pull from the summary fields in it's own sheet. If you added them to the individual project sheets, then do you just create a regular report to pull the data together?

Like I said, there's something going on and I just couldn't follow exactly your steps for this solution. All help is appreciated.

Thanks.

@Paul Newcome That makes sense. It's unfortunate that it has to happen this way, as it completely destroys the benefits of the Sheet Summaries. An issue I just ran into today also destroyed it. I have a sheet for each project, with a pm for each project. Some of them deleted rows on their sheet because their project didn't need those steps. After they did this, I copied my sheet summary fields and formulas, but they no longer work correctly since there are "missing" rows. I wish the software was a little more intuitive and recognized that I don't care about a specific cell's data, I care about what that data represents. If a row is deleted, I want it to move the new formulas up to accommodate. It seems like your solution of adding new columns with just one piece of information at the very top, is a work around for this. Then when rows are deleted, the formulas at the very top would update automatically.

Thanks again.