Daily Summary Field Tracker

Hello Community,

Having trouble coming up with a solution for this. I have a sheet with a bunch of data and many sheet summary fields. The fields are mostly counts or sums of rows based on a criteria. Like a count of how many rows with a checkbox column with a check. A have a dashboard which shows all the needed info. This all works fine.

What I'm being asked for is essentially daily (or some time period) 'snapshots' of the sheet summary fields so they can see progress. Its easy to see the live info on the dashboard or report but they want to see progress like On Monday x (sheet summary field) number was complete, etc. So they can see say Mon the value was x, Wed was y, Friday was z. Like a history of the sheet summary fields. Does that make sense?

I was thinking some kind of automation that can run daily and it grabs sheet summary values and stores them so i can report them.

Any ideas how to do something like this? I'm hoping its simple and I'm just not seeing the obvious. Thanks!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Steve_Mitchell

    I hope you're well and safe!

    You could either use a section of the sheet or add so-called helper columns for the values and add them, for example, on row 1 (which can be hidden), and then add a so-called helper sheet where you keep all the history which would be copied from the sheet with a copy row workflow. You can then use the history data as needed.

    Make sense?

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Steve_Mitchell
    Steve_Mitchell ✭✭✭✭

    Hi @Andrée Starå, thanks for the reply.

    I was hoping for something simpler. The main issue I have with this approach is adding another row since the only way to hide it is with a filter and people will be using other filters so that row will be unhidden alot. I'm also concerned about it being used in calculations, etc. I'm also already using a bunch of hidden helper columns and rather not add around a dozen more.

    Too bad I can't reference summary values via cell linking, or have an automation that ran daily that could stuff the summary values into another sheet or something.

    Any other ideas out there?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest a second sheet to house the counts and sums instead of sheet summary fields. Then you can use the daily Copy Row automation to push this data over as static to the "historical" sheet for charting.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com