How to auto populate metrics sheet?

Edgar1234

Hello, i'm looking for help to auto populate a Metrics sheet with data from an Intake Form that would provide average values arranged by Vendor/ Quarter/ Year?

Adam Murphy

You can likely do what you want with a report using summarization and grouping. In order to "automate" a metrics sheet you have to create the formulas and cross-sheet references to do so (in your case sounds like you would use AVERAGEIF or COUNTIFS formulas). Hope this helps!

Edgar1234

Thanks, Adam for the prompt response.

I was able to auto populate the averages for the criteria columns using:

=AVG(COLLECT({Intake Form Range1}, {Intake Form Range2}, =Year@row, {Intake Form Range3}, =Quarter@row, {JDM Scorecard Intake Form Range4}, =Vendor@row))

However, it required that I "manually" populate the 1st 3 columns of the metric sheet i.e. Year, Quarter, and Vendor.

I'm trying to avoid having to manually populate these 1st 3 columns.

Brian_Richardson

You can automatically create/update a metrics sheet with the criteria by using the Pivot app (a paid addon). Pivot your metrics sheet by month and year. Pivot will create a sheet that you can then add additional columns and calculations to.

From a native Smartsheet perspective, with no addons, Adam has outlined the best option. You need your metrics sheet to have some set of data to use to know how to collect the results. There's not really a way around that without using Pivot.

There's one other option but it's not much less manual. Instead of a separate sheet for metrics, you can create rows on your detailed sheet and indent the other rows underneath. So you could create a row for each quarter, indent the detailed rows under that, and then use formulas on the "quarter" row to Average the results like =AVG(Children([Criteria 1]@row))