I am having a go at building a staff roster with excl 2007, I am stuck and not sure where to start.
In short; I want to calculate the “hours worked” per month for each person on the roster. Unfortunately I would like to have the formula and result situated in sheet 1 while deriving data from sheets 1 and 2. My reason for wanting it this way is to ensure that whenever shift start/finish times are altered or shifts are added/deleted then the formula wont need to be altered. So; Sheet 1 has the actual roster with all the presented info arriving from Vlookup/data validation type functions. Sheet 2 has tables containing all the relevant info (name, eft, shift codes, shift start/finish times, shift hours, etc) which can be altered whenever necessary. I think there are 3 key steps to this calculation.
- I want to (somehow) have a solution which starts in the first cell (and steps through each successive cell) of sheet 2's shift code column, identifies the cells unique alpha-numeric shift code and then counts the instances of the code in each persons row on sheet 1.
- I then want the sum of each unique alpha-numeric shift code to be multiplied by the shift length (hours worked) which is calculated on sheet 2.
- Lastly, I want to add together each alpha-numeric shift codes "hours worked" sums to give an overall monthly hours worked for each person.
As previously stated I do not know where to start with this so any suggestions are welcome. Regards