Auto-populating a calendar in Excel based on conditions from a data table on another sheet

[scotthunter]

Hello, I'm new to this forum and hope that someone can help me with what I am trying to achieve! I just can't think logically enough to work this one out for myself!

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

Hopefully this will make it clearer. So the Work Schedule:

[TABLE="width: 2734"]
<colgroup><col><col><col><col><col><col span="2"><col><col span="2"><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Job[/TD]
[TD]Project Manager[/TD]
[TD]Client[/TD]
[TD]Site[/TD]
[TD]Job Description[/TD]
[TD]PW[/TD]
[TD]LEZ[/TD]
[TD]Tip Site[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="colspan: 3"]Days[/TD]
[TD]W-End[/TD]
[TD]Duration[/TD]
[TD]Status[/TD]
[TD]Tanker[/TD]
[TD]Equipment[/TD]
[TD]Vehicle 1[/TD]
[TD]Vehicle 2[/TD]
[TD]Vehicle 3[/TD]
[TD]HGV/Other[/TD]
[TD]Site Manager[/TD]
[TD]Plant Operator 1[/TD]
[TD]Plant Operator 2[/TD]
[TD]Site Operative 1[/TD]
[TD]Site Operative 2[/TD]
[TD]Site Operative 3[/TD]
[TD]Enviro Consultant[/TD]
[TD]Enviro Assistant[/TD]
[/TR]
[TR]
[TD]0376-14[/TD]
[TD]Mark Sluman[/TD]
[TD]Ryder Plc[/TD]
[TD]Various[/TD]
[TD]Annual Tank Inspection Programme[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Valgrove – West Midlands[/TD]
[TD]21/04/14[/TD]
[TD]23/04/14[/TD]
[TD]Mon[/TD]
[TD]-[/TD]
[TD]Wed[/TD]
[TD]No[/TD]
[TD]3[/TD]
[TD]Complete[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0479-14[/TD]
[TD]Scott Hunter[/TD]
[TD]DB Schenker[/TD]
[TD]Toton[/TD]
[TD]Environmental Assessment[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Valgrove – West Midlands[/TD]
[TD]21/04/14[/TD]
[TD]28/04/14[/TD]
[TD]Mon[/TD]
[TD]-[/TD]
[TD]Mon[/TD]
[TD]No[/TD]
[TD]6[/TD]
[TD]Active[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hire Van[/TD]
[TD]AD57 JZD[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Scott Hunter[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Scott Hutner[/TD]
[TD]Kris Maxwell[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 2734"]
<colgroup><col><col><col><col><col><col span="2"><col><col span="2"><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Calendar:

[TABLE="width: 857"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 9"]JOB DETAILS[/TD]
[TD]21/04/14[/TD]
[TD]22/04/14[/TD]
[TD]23/04/14[/TD]
[/TR]
[TR]
[TD]JOB[/TD]
[TD]PM[/TD]
[TD]Client[/TD]
[TD]Site[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD="colspan: 3"]Days[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]0376-14[/TD]
[TD]Mark Sluman[/TD]
[TD]Ryder Plc[/TD]
[TD]Various[/TD]
[TD]21/04/14[/TD]
[TD]23/04/14[/TD]
[TD]Mon[/TD]
[TD]-[/TD]
[TD]Wed[/TD]
[TD]Various[/TD]
[TD]Various[/TD]
[TD]Various[/TD]
[/TR]
[TR]
[TD]0479-14[/TD]
[TD]Scott Hunter[/TD]
[TD]DB Schenker[/TD]
[TD]Toton[/TD]
[TD]21/04/14[/TD]
[TD]28/04/14[/TD]
[TD]Mon[/TD]
[TD]-[/TD]
[TD]Mon[/TD]
[TD]Toton[/TD]
[TD]Toton[/TD]
[TD]Toton[/TD]
[/TR]
[TR]
[TD]0186-14[/TD]
[TD]Nigel Friend[/TD]
[TD]Safety-Kleen[/TD]
[TD]Sandquay[/TD]
[TD]22/04/14[/TD]
[TD]22/04/14[/TD]
[TD]Tue[/TD]
[TD]-[/TD]
[TD]Tue[/TD]
[TD] [/TD]
[TD]Sandquay[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0521-14[/TD]
[TD]Nigel Friend[/TD]
[TD]Addicott Electrics[/TD]
[TD]Dorchester[/TD]
[TD]22/04/14[/TD]
[TD]22/04/14[/TD]
[TD]Tue[/TD]
[TD]-[/TD]
[TD]Tue[/TD]
[TD] [/TD]
[TD]Dorchester[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0658-14[/TD]
[TD]Darren Smith[/TD]
[TD]Interflex Scotland[/TD]
[TD]Not specified[/TD]
[TD]22/04/14[/TD]
[TD]22/04/14[/TD]
[TD]Tue[/TD]
[TD]-[/TD]
[TD]Tue[/TD]
[TD] [/TD]
[TD]Not specified[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]