One formula in your Summary sheet, cell C5:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(C$3,INDIRECT($A5 & "!$B$6:$AB$6"),0))
Copy down and across to column ZZ.
The names in column A of the Summary sheet must match exactly the names on the tab of each sheet.
Your formulae in cells AC5:AD8 differ slightly from the above by looking up the numbers in row 4 instead of the text in row 3, I've adhered to that,
AC5 formula:
Code:
=INDEX(INDIRECT($A5 & "!$B$40:$AB$40"),MATCH(AC$4,INDIRECT($A5 & "!$B$7:$AB$7"),0))
but I suspect there's no reason they can't be the same as the formulae in columns C to ZZ.
Streamlined your formula in C11 to:
=SUMPRODUCT(C$5:C$8,$B$5:$B$8)
copied across.
Please double check the results are what you're expecting in the attached.