Sum cell values within the same range in multiple sheets with a criteria applied

[nikenis]

Hi guys,

Here is the task.
I have an attendance tracker where I have different reports, controls and a staff listing sheet.
In the staff listing, I have, obviously, all active staff with a unique ID (col A) that is hyperlinked to their own sheet named as such (starting from 1). Another column is staff position (Col. D), which will become a criteria later.
In all sheets named "1" onwards, there is a range B11:AF22 that represents the calendar year (day as x-axis, month as y-axis) where employee rotation are uploaded. When Holidays are requested the shift is transformed to "8" for 1 annual leave or "PH" for a Public Holiday.

I would like to create a new file with three sheets:
1. Inspector (col.D in staff list as criteria: SI and I)
2. Dealer Inspector (Col.D as DI)
3. Dealer (Col.D as D1, D2, D3, T1, T2, T3, T4, T5)

In those 3 sheets, we will find the same range B11:AF22 resized to B3, representing the calendar year. In each cell, representing one day of the year, I would like to display the number of Holiday already taken.

In clear, I need to sum up the number of hours of holiday booked + PH in the very same template used for individual in a new file. Knowing that the last sheet is not definite as we are constantly hiring. No much of a turn over but went from 220 to just under 300 employees within 8 months operation.

I hope this is clear enough, if not drop me your questions.
Any thoughts to tackle this?

Cheers,
Nikenis

[nathansav]

can you send an example?

[nikenis]

OK, I will try to sample the file without employee names...
This is a light version of it.
The sheets Dest Inspector, Dest DI, Dest Dealer are the one that should be in a new file.

Cheers,
Nikenis