How to summarize data from multiple sheets without manually linking each cell?

CEP

Hello,

I'm working on a project with KPIs that will be entered into ~50 individual sheets. I want to be able to roll-up these KPIs into one summary view, but am running into trouble when I pull them into a report because I can't perform calculations in a report.

One KPI is Overtime %, so I can't just take the average of each overtime % in the 50 individual sheets. I need to be able to take the sum of all the OT labor hours / the sum of the total labor hours across all the sheets. The only way I can think to do so would be to manually link each cell into another sheet to perform the calculation. Given my large data set however this won't be possible. Is there another way to do this?

Thanks for your help!

Paul Newcome

The sum across all sheets divided by the sum across all sheets would be the average % though.

CEP

@Paul Newcome Yes, but I'm trying to set it up so it will update automatically. If I get both sums by using report summary, I would have to copy and paste those numbers in order to take the ratio. More data points are going to be added weekly, so I'm hoping to find a workaround to automate this so I don't have to update the ratio each week.

Paul Newcome

If you have the percentage in the individual sheets, you can use the grouping and summarizing features within the report to get the average of the percentages.

CEP

@Paul Newcome Taking the average of percentages results in an inaccurate number due to sample size.

I did end up finding a way to pull in the OT and total labor hours into a new sheet using an xlookup that I can drag down and it will automatically update.