Daily Summary Field Tracker

Steve_Mitchell

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!

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!

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

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.