Summarise Class by Time Matricies

[flywire]

I'd like advice how to do the following reports. I need summaries of matrices as 3 time period columns by 5 class rows for each matrix sheet on a sheet.

My matrices are 10 class columns with 24 time period rows containing integer values. The matrices are within separate existing sheets of various layouts but the matrix start cell can be visually identified for the set of sheets and the cell copied t the secondary sheet with the following lookup tables:

<tbody>
[TD="align: left"] Column [/TD]
[TD="align: left"] Group [/TD]

[TD="align: right"]1[/TD]
[TD="align: left"]G1[/TD]

[TD="align: right"]2[/TD]
[TD="align: left"]G2[/TD]

[TD="align: right"]3[/TD]
[TD="align: left"]G3[/TD]

[TD="align: right"]4[/TD]
[TD="align: left"]G3[/TD]

[TD="align: right"]5[/TD]
[TD="align: left"]G3[/TD]

[TD="align: right"]6[/TD]
[TD="align: left"]G4[/TD]

[TD="align: right"]7[/TD]
[TD="align: left"]G4[/TD]

[TD="align: right"]8[/TD]
[TD="align: left"]G4[/TD]

[TD="align: right"]9[/TD]
[TD="align: left"]G5[/TD]

[TD="align: right"]10[/TD]
[TD="align: left"]G5[/TD]

</tbody>

<tbody>
[TD="align: left"] Time [/TD]
[TD="align: left"] Period [/TD]

[TD="align: right"]00:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]01:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]02:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]03:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]04:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]05:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]06:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]07:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]08:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]09:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]10:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]11:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]12:00[/TD]
[TD="align: left"]Lunch[/TD]

[TD="align: right"]13:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]14:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]15:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]16:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]17:00[/TD]
[TD="align: left"]Day[/TD]

[TD="align: right"]18:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]19:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]20:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]21:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]22:00[/TD]
[TD="align: left"]Night[/TD]

[TD="align: right"]23:00[/TD]
[TD="align: left"]Night[/TD]

</tbody>

I have a complicated CSE process with hard-coded cell values in the summary sheet but it does not allow different matrix layouts to be loaded.

The three rows above the matrix have merged cells and will not work with a table.

 

[flywire]

My summaries of matrices currently use fixed CSE formula for each class like:
{=SUM(IF($Lookup.$A$14:$A$37="Day",$MatrixSheet1.$E$14:$G$37))*D$5/D$15}