Copy of roster pattern2.xls

Hi,

I am trying to create a roster which includes Fly In Fly Out on a 19 on/9 off, and Residential workers on Mon - Fri. This will be the basis of our Planning, Admin and Training personnel being able to track and arrange our crews for works, payroll submission followups etc.

With regards to the FIFO workforce, when a cell value in the roster area is changed to a certain value (in this case, if would be Flight Day In "FDI" as the value that triggers the fill across) I need for the next 27 cells to populate the roster (*day 1 - Flight day in; then 17 work days to be indicated by the accomodation they are staying at - so i can track how many rooms spare we have at the accomodation; day 19 - Flight day out; then 9 days of R&R). With regards to residential, they only work Mon - Fri, but occasionally there will be weekend work - hope the attached workbook make sense..

A B C D E F G H I.... W X.... AD AE
Name Position Roster Accom Thur 1/1/12 Fri 2/1/12 Sat 3/1/12 Sun 4/1/12

Joe B Supv FIFO Walkabout FDI WA WA WA WA FDO RR RR RR
Amy K Safety FIFO Frogs FR FR FDO RR RR FDI FR FR
Ben H Fitter Resi Resi RR RR

These need to be populated 'automatically' as eventually I will be entering data for over 100 people, and as the Planner, simply do not have enough time! Also, it needs to be manual and set up so that if I enter another "FDI" mid-swing (i.e. they cut their holiday short) then the previous cells are not affected, but the patter repeats moving forward. The same would be for the Residential ("Resi") - that with the occasional weekend work, it does not affect the other weeks/days entries.

The other thing I need to do is to create a formula in the accommodation count cells which not only counts the number of rooms used per facility (countif function used to look up all cells in column which meet the accom ID - i.e. "FR"), but also counts the "FDI" & "FDO" cells. I have the countif working, but cant get the formula right so that it doenst count ALL "FDI"/"FDO", just those which correspond with the accom (and again, only if FIFO).

There will also be a number of manual inputs which would be adhoc (RDO = Rostered Day Off, RR = R&R, AL = Annual Leave, SL = Sick Leave etc.) which I dont want to disrupt the rest the input pattern when updates are entered, and would like to be able to set colours for cells and fonts accordingly (though making it pretty is not essential at present).

I have looked up everything I can and tried numerous things (some of which is still in the VBA editor) and am just stuck - any help would be much appreciated!

Suzan