How have folks summarized statistics from multiple sheets to feed a chart widget?

Dale Murphy
Dale Murphy ✭✭✭✭✭✭

Our marketing folks use a new sheet for every campaign. During the span of a few months, there could be several dozen such sheets active. They are all built from a template and reside in the same folder .

Within each sheet there are several summary fields that can indicate number of tasks, number of unstarted tasks, number of late tasks, and so on.

The marketing leaders would like to know in aggregate the progress of the campaigns. Has anyone successfully "added up" the summary field metrics from a dozen or more sheets and produced a chart widget of them? (I know how to build a summary report, but that doesn't provide the graphic visualization. )

Dale

Best Answer

  • Stefan
    Stefan ✭✭✭✭
    Answer ✓

    Hi @DMurphy,

    manual work low tech option: Create a summary-sheet with one row for every campaign sheet and cell-link all the needed values from the source sheets. Use the summary-sheet as source for your chart widget in your dashboard. This allows you to manually sort the columns and their names to your liking.

    Second option is to get Control Center (because you already use templates) where you can automate all this:

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Answers

  • Stefan
    Stefan ✭✭✭✭
    Answer ✓

    Hi @DMurphy,

    manual work low tech option: Create a summary-sheet with one row for every campaign sheet and cell-link all the needed values from the source sheets. Use the summary-sheet as source for your chart widget in your dashboard. This allows you to manually sort the columns and their names to your liking.

    Second option is to get Control Center (because you already use templates) where you can automate all this:

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @Stefan Yes, I use the summary sheet or "metric sheet" most of the time. The wrinkle here is that the group involved builds new sheets all the time, so keeping the overview report up-to-date is a challenge.

    I agree, Control Center is probably the solution. But right now the middle and senior managers still argue over giving a license of SS to PMs and product managers ;) so stepping up for Control Center is a stretch. This situation is another use case though.

    Thanks for the input.

    Cheers,

    dm

  • Stefan
    Stefan ✭✭✭✭

    Hi@DMurphy,

    ok, some ideas.

    Maybe you could build a workflow into the template that triggers only when a new sheet has been created from it. Kind of notification for you about adding it to your metrics sheet.

    Regarding budget for more Smartsheet. I sometimes convinced managers by offering custom built dashboards for them. A common requirement for (decision making) dashboards is a reliable data source with standardised procedures for adding/removing source sheets.

    That's what Control Center offers (and much more). And by the way, it eliminates/reduces the time currently required for rollout of new sheets and their integration in reporting.

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.