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!