I have uploaded the roster I am working on (I hope );

I am trying to create a roster on the Roster1 page which only contains information/values drawn from the Lists page.
On the Roster1 page, cells J9:J35 to AQ9:AQ35 are populated with the shift codes from column F7:F52 on the Lists page, with each row representing a single persons total restored shifts.

The column AW9:AW35 on the Roster1 page is intended to display a total restored hours. The problem is, I do not know where to start in creating a solution that works from the Lists page so shift start/finish times can be altered, shifts added/removed etc and the formula will automatically pick up the alterations and display the correct hours rostered total.

1. I want to (somehow) have a solution which starts in the first cell (and steps through each successive cell) of the shift code column (Lists F7:F52), identifies each cells unique alpha-numeric shift code and then counts the instances of the code in each persons row (e.g. J9:AQ9) on Roster1.
2. I then want the sum of each persons unique alpha-numeric shift code to be multiplied by the shift length (hours worked) which is calculated on Lists page (I7:I52).
3. Lastly, I want to add together each alpha-numeric shift codes hours worked sums to give an overall monthly hours worked for each person (Roster1 A9:A35).

Regards...

P.s. We are using a German version of Excel 2007 so we use ; instead of commas in our formulas.

Attached Files

Bookrstr1.xlsx (35.9 KB, 12 views)