Sheet setup for scalable data
Looking for a best practice to set up a scalable (target vs. actual, month-over-month) sheet - There are 15 specific varying metrics (e.g. numbers, $, %) collected monthly by different people. Each metric will require both actuals for the prior month and a target for the next month. I would like to show both monthly metrics and trends over time. Any suggestions on the best way to set up the sheet(s) so that I can easily create reports & show in a dashboard?
Answers
-
You could set up a second sheet with a copy row automation on the first to copy rows to the second on a recurring basis. This will give you those static metrics for historical data that can be used for trending and whatnot.
-
Thanks Paul. I though about that, but how would that be different than creating reports from just the first sheet, besides keeping the first sheet clean (e.g. w/out prior data)? I also thought about creating a different sheet for each metric, but that would be a lot of sheets.
-
The reports will not capture historical data which means you won't be able to show trends.
-
Can you suggest how I would make that work? I have an intake form for the data, it populates into the intake sheet, then automatically moves to the bottom of a different "summary" sheet. Would it be best if there was a unique summary sheet for each metric or ? Trying to figure out how I can best capture month over month data.
