Best way to calculate data across 100 sheets with same structure

gwson

Working on a project that will have 100 or so sheets with exact same structure. I need an efficient way to be able to calculate data SUM, COUNTS, AVG across 100 sheets in one metric sheet. What is the best way to accomplish this without the need to reference each individual sheet? Is there a way to run metrics on a report that pulls all the sheets together in one view? Other?

My backup plan is to combine (copy paste) all 100 sheet at some point when data entry mapping is complete and then run my variables against it.

AravindGP

Hi @gwson

Your backup plan is the way to go. Since they have the same structure, I am assuming that you're going to be building one sheet and then using the "Save as New" function to replicate the sheets to get to 100. If the assumption is correct, instead of manually copying and pasting the data, you can look at creating a workflow in the sheets to copy data into the new consolidated sheet based on a condition (if possible). You can then reference this one sheet alone to get your metrices. Do note that if you anticipate frequent data changes, then the copy workflow or even manual copy and paste will not have latest data when you're referencing the consolidated sheet.

gwson

Suppose I add automation to each individual sheet to copy entries into the combined sheet as they get updated. How would i ensure that the entries in the combined sheet are unique. In other words, is there a way for the combined sheet to delete old entries or mark themselves old, of for the incoming entries to overwrite existing ones. Each sheet has a specific count of rows and there cannot be duplicates otherwise the calculations won't work.