Hi all,
I'm not sure if a LOOKUP formula is the best way to solve my problem, but I have attached a spreadsheet with my work roster on it.
The 'Roster' sheet has our monthly shifts. The letters for these shifts can't change (I have been told that LOOKUP doesn't do case sensitive).
The 'Shift Times' sheet indicates how many hours each of the different shifts go for.
The 'Fatigue Tracker' outlines how many hours we work each day and then works out how many hours we have worked in the last 7 and 28 days. We have maximums we can work during these periods, so what I am trying to do is predict the number of hours each person will work in the future, so we can identify if someone is going to exceed those maximums well before it happens. This then allows us to roster that person for an extra day off for example.
For Smith, the hours in B2:AF2 have been entered manually but I would like to be able to have it done automatically based on what shift is in the cell range B3:AF3 in the 'Roster' sheet.
If this is possible, the other problem I have is that when the person is rostered on an N (night) shift, they actually only work 2.5 hours on the day of the N shift and 5.5 the day after. Therefore, I'd like to be able to return 2.5 in the relevant cell in the 'Fatigue Tracker' sheet, but return 5.5 in the cell next to it as well. If this is impossible, then I'd put up with just having the 8 hours returned in the cell of the N shift.
Another problem is that the order of names in column A in the 'Fatigue Tracker' sheet won't change, but the order will change in column A of the 'Roster' sheet. I know this potentially makes it harder, but I'm hoping someone out there in Excel land can help me out.
I hope I have explained the situation clearly enough.
Cheers!
Bookmarks