Shift Pattern Help

Hi all,
I am attempting my first Excel sheet to try and make the yearly shift pattern a little easier to create/use as it's currently a complete manual operation.
I have attached what I have done so far but would just like some input/guidance on how to achieve what I need.
We have 4 shifts which repeat every 8 weeks and unlike most although there is a pattern of sorts it's not a standard one that I have ever come across anyway.

Within the workbook I have 2 sheets, the first, Yearly, is what I am trying to populate with the corresponding colors of the shift that they will work, January is already filled in manually and is correct as to how the pattern should look.

I just need each day of the week populated with either A,B,C or D in the correct order based on the pattern within the sheet, Shifts.

I think i need a combination of VLOOKUP,INDEX and IF formulas but this is where I am now at a loss and am asking for any help anyone can give.

Thanks in advanced

Welcome to the Forum.

Can you please clarify the use of the Drop-down options in rows 3 and 4. Are these connected to the start dates or are the starts dates put in manually? If the drop-down influenced the dates then populating the rest of the sheet would be easier i.e. you could select a week, that would change the start date and then using that info the sheet could be auto-filled.

Cheers,

Hi,

My plan was to have the user fill in the 5 options along the top yes,
Year: 2014
The date each the shift starts it's first full week 6/1/2014 (this is a manual typed filed at the moment but can be changed)
What week number the shift pattern should start on: Week 8
And weather this shift starts on days or nights: Days

The information in the dropdowns are all being pulled from the sheet labeled Shifts.

Thanks for taking the time to have a look, much appreciated.

Take a look at this thread:

http://www.excelforum.com/excel-gene...t-pattern.html

That was for a 3-shift pattern, but perhaps you can adapt it for yours.

Hope this helps.

Pete

OK so the 8 week cycle is that repeated for the rest of the year as shown on the "Shifts" sheet or is this only for the first 8 weeks?

Also the drop-downs are taken from the "Shift" sheet but what effect do they have on the "Yearly" sheet, because changing them seems to do nothing other than show week 5 or week 8?

Plus is doesn't help that Team "A" starts with week 8 ... it's kind of confusing.

@ Pete_UK - very cool sample ... works well.

@ Slain - I think you need to do more extensive work on your layout to get the results that you seek for the year.

Cheers