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



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!