Calculation Sheet for multiple sheets

jg124

Hi Guys

I have a calculation sheet that is currently bringing in data from 3 external sheets in order to be able to create reports and graphs for dashboards etc

My problem is that I am conscious I will reach my cell link limit, along with the fact that 3 or 4 more sheets will soon have to be included in calculations, meaning I will have to add in calculations and cell link manually.

Our organization has the Pivot App add on - is this the solution to my problem?

Will the Pivot App enable me to create a calculations sheet from multiple other sheets and somewhat automate what is a manual process at the minute?

I am somewhat confused as to what the purpose and benefits of the Pivot App is

Any info much appreciated !

markkrebs

Pivot works similar but not as feature rich as Excel pivot. Your sheet structure needs to be the same so you can rollup the raw data into a report and then pivot off the report. I'd have to look at your sheet structure but I'd structure the metrics sheet to have generic column names and then use the primary as the metric and then bring in the summary data with formulas in the generic columns. That provides flexibility without hitting reference link limits. Hope that helps!

markkrebs

Pivot works similar but not as feature rich as Excel pivot. Your sheet structure needs to be the same so you can rollup the raw data into a report and then pivot off the report. I'd have to look at your sheet structure but I'd structure the metrics sheet to have generic column names and then use the primary as the metric and then bring in the summary data with formulas in the generic columns. That provides flexibility without hitting reference link limits. Hope that helps!

jg124

Hey @markkrebs

Thanks for your reply

Each sheet is for a specific lab and resides in a different workspace, however all the columns name should/can be the same to roll up

So are you saying have a separate metric sheet for each lab- and then use Pivot App to merge to help generate my graphs for the dashboard? Sorry if I am misinterpreting!

Thanks

markkrebs

If they have the same layout then I'd make a report that goes across all the workspaces then use Pivot to summarize by your dimensions (Month, Year, etc...) OR if you just need to group by a column of 2 you can use the new grouping / summarize functionality of SS reports. We can have a quick call is needed.

jg124

Thanks for your help @markkrebs I will give that a go today and see how I get on :) Might take you up on that offer of a quick call if needed haha