How to aggregate sheet summary data into another sheet

slaranda

Hello,

I am currently working on developing a workspace to track system testing. The way the workspace is currently set up is having a summary level sheet that has each script, and then a folder per script. Within each script specific folder, there is a script summary sheet and a test script sheet. There can be multiple test script sheets if the test is determined it needs to be repeated.

The data I am trying to aggregate is from the test script sheets. Each test script sheet has summary sheet values. I would like to aggregate the test script sheet summary values both within each test script folder AND across all test scripts. I would like these values to go into another sheet for downstream use.

I do see that I can create a report from the sheet summaries, but the reports are a dead end and do not allow further analysis of the data (ie I can't pull the report info into another sheet or into a subsequent report). I also can't use the reports to display metrics on my dashboard, which is the whole point of the sheet summary values.

Manually calculating these summary values into a new sheet is not a viable option because it would require end users to mess with formulas and set up manage many cross sheet references (likely 30+ per round of testing). This workspace will be templatized and the name and number of sheets will change each time the template set is used.

If there was a way to dynamically set up a cross sheet reference by storing the sheet name in a helper column that could be a solution, but I have found no examples of that. Another option would be to be able to pull the data from my sheet summary report into sheets or other reports, but I have not found examples of that either.

Any advice on the best way to aggregate data across many sheets without having to manually set up and manage many cross sheet references would be appreciated.

Ipshita

@slaranda - If you wish to store sheet name as a link in a cell, that's possible. Just copy the hyperlink of the sheet from the address bar, right click on the cell where you want to enter the sheet link and use the Hyperlink option. Here you can enter the URL and a name for the worksheet. For example -

I can suggest other options for sheet summary data to be used by another sheet, let me know if you need more help and I will advise.

Cheers!

slaranda

@Ipshita Thank you! Yes, please share how to use the sheet summary data in another sheet. I couldn't find any examples of this when I searched.

Ipshita

@slaranda Sure :)

So the easiest way to do that is, in your working sheet where you have all the summary field values, just create columns for each of these values, this is how you do it - =[ ]# name the column whatever you like best.

Now, these values can be cell linked into sheet and that's how you can manipulate the data in your other sheet.

Hope this helps.

Cheers!