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.
- 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.
- 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).
- 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.