Best way to calculate data across 100 sheets with same structure

gwson
gwson ✭✭✭✭✭
edited 10/24/23 in Formulas and Functions

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.

Answers

  • AravindGP
    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • gwson
    gwson ✭✭✭✭✭
    edited 10/30/23

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!