Auto Populate Calendar From Data Table

Hi

I've a straight forward shift pattern rota in a vertical table - I'm trying to have it display automatically in a visual calendar to make it easier for people to see and understand.

Online Example.xls

In the attached, "2014 Calendar" is just a calendar template I found online and I've a drop down in G1 which links to the table in "Rolling Rota".

If I select "Bob" then I am trying for all of his shifts to populate, matching the date in the calendar sheet against the data in the rota sheet.

That's the main part and I'm stumped. Have searched through the forum here and tried lots of previous examples but can't find one that works. I'm not at all precious over the calendar layout - if there is a better one for this function then I'll take it.

I'd like to future proof this if possible, so if it's at all possible to make the calendar itself incorporate a drop-down (i.e. in B2) where you can select years and the calendar then relabels itself (i.e. the correct date by day column; I guess the formatting of the borders by month would be manual.... but that'd be easy to do)

Thanks for any help, advice or pointing at a resolution elsewhere!
Tom

Cool. Likes like this is right and is all working - will have a check and see. Thanks!