Is there a method to copy sheet summary values to another sheet?

Ronald Moyer

I'd like to create trend reports on project data over time. For example, I use a sheet summary to total completed story points for a project. I update the project metrics in my sheet every 2 weeks. I need to capture the value of the summary field for each 2-week sprint so I can develop trend reporting (velocity, etc…) over the duration of the entire project.

Any approaches or methods anyone can suggest?

Thanks!

Louis1

Hi R,

Not exactly sure how sheet summary will help you capture trends overtime however my approach would be :-

1. Create a helper sheet that will hold the fortnightly values (by category). This sheet will hold the values you are interested in (which will have the same column names as the source sheet). This helper sheet will also have the required logic (formulas) to calculate the trend in other (new) columns. You will need to set this up.
2. On the source sheet ensure you have the values you are interested in on a row. This source sheet should have an automation that "copies the required row" ie the calculated values, to the helper sheet every 2 weeks. Note that the calculated values are transferred as text (fixed numbers, and not formulas).
3. Once copied to the helper sheet the kpi/trend formulas on this helper sheet are applied automatically to the new row added via the automation and trends calculated automatically.
4. You can then have logic in sheets and or reports that access the latest calculated trend and date spanning last x-periods.
5. Note your helper sheet is just that - ignore any other colum data that comes across.

Hope this helps !

L.

Louis1

Hi R,

Not exactly sure how sheet summary will help you capture trends overtime however my approach would be :-

1. Create a helper sheet that will hold the fortnightly values (by category). This sheet will hold the values you are interested in (which will have the same column names as the source sheet). This helper sheet will also have the required logic (formulas) to calculate the trend in other (new) columns. You will need to set this up.
2. On the source sheet ensure you have the values you are interested in on a row. This source sheet should have an automation that "copies the required row" ie the calculated values, to the helper sheet every 2 weeks. Note that the calculated values are transferred as text (fixed numbers, and not formulas).
3. Once copied to the helper sheet the kpi/trend formulas on this helper sheet are applied automatically to the new row added via the automation and trends calculated automatically.
4. You can then have logic in sheets and or reports that access the latest calculated trend and date spanning last x-periods.
5. Note your helper sheet is just that - ignore any other colum data that comes across.

Hope this helps !

L.

Ronald Moyer

Thanks Louis. I think this approach may work for me. I'll post again if I get stuck.

Thanks again!