Metrics Sheet Pulling from Reports

Techspan CT

Hello All,

I'd love to know if its possible (or a workaround) for creating a metric sheet to use in a dashboard using data from reports.

We have 90+ Sheets that we can easily pull data into a report but its hard to get that into a meaningful chart. One specific example is we have 90+ sheets with rows that contain a rating from 1 to 10 and the date it was entered. We can pull it into a report showing the name, rating and date but cant seem to find a way to show this on a chart with an average rating across all entries by date.

What I would love to do is create an index/collect/match formula that can return an average of scores based on the date pulling from the report. I know I can build this out with individual references to all 90+ sheets but it is very tedious and not very flexible!

Any Ideas?

Paul Newcome

At this time there is no way to use a formula to reference a report. I would suggest submitting a product enhancement request when you are able as that would be a pretty useful feature.

In the meantime, there are a couple of different ways to consolidate this data into a more useful format. It sounds like all of the sheets contain the same data that you are wanting to pull and they are all in the same layout (similar to using a template). If that is the case then the premium app Control Center would be an option. If that won't work for you, then you can use cell linking to link each sheet to a master sheet and then run your calculations based on this consolidated data. It would be a bit of work pulling in cell links from 90 existing sheet, but it should only take an extra minute or so as new projects are added to link each new sheet to the master sheet as they are created.