Create master list sheet for multiple individual sheets and count the data from individual sheets

aoi

Hi there

I have a question to how to automatically link the count formula from one master list to individual sheet.

Below is my workflow. I have created multiple individual sheets for each of my program to track the program status, (Pink Heading) Sheet 1 - Program 123, Sheet 2 - Program 124 …

Then, I created a (Blue Heading) Sheet 3 - Master List to keep track on the number of issues from each program. I put the hyperlink of the individual sheets to the program column in Sheet 3 - Master List.

For Total Issues column, I am using "Count" formula and reference to the individual sheet of Status column. If Status column has a data, it is counted as 1 issue.

For Open Issues column, I am counting the issues reference to the individual sheet of Status column where Status is not "Solved"

Currently, I have to create the formula of each program in Sheet 3 - Master List one by one by reference to the individual sheet. Is there an automated way I can capture the data from the individual list because there are many programs need to be tracked?

Thank you!

Brian_Richardson

You're doing it correctly, with the base tool. When you start to scale, then the only way to automate this is to use an addon like Control Center. Control Center will automatically create cell links and references for you into a summary sheet, which you can then do further math on.