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:

Job

Project Manager

Client

Site

Job Description

PW

LEZ

Tip Site

Start

End

Days

W-End

Duration

Status

Tanker

Equipment

Vehicle 1

Vehicle 2

Vehicle 3

HGV/Other

Site Manager

Plant Operator 1

Plant Operator 2

Site Operative 1

Site Operative 2

Site Operative 3

Enviro Consultant

Enviro Assistant

0376-14

Mark Sluman

Ryder Plc

Various

Annual Tank Inspection Programme

Valgrove – West Midlands

21/04/14

23/04/14

Mon

-

Wed

No

3

Complete

0479-14

Scott Hunter

DB Schenker

Toton

Environmental Assessment

Valgrove – West Midlands

21/04/14

28/04/14

Mon

-

Mon

No

6

Active

Hire Van

AD57 JZD

Scott Hunter

Scott Hutner

Kris Maxwell

<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>
</tbody>

<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>
</tbody>

Calendar:

JOB DETAILS	21/04/14	22/04/14	23/04/14
JOB	PM	Client	Site	Start	End	Days	Monday	Tuesday	Wednesday
0376-14	Mark Sluman	Ryder Plc	Various	21/04/14	23/04/14	Mon	-	Wed	Various	Various	Various
0479-14	Scott Hunter	DB Schenker	Toton	21/04/14	28/04/14	Mon	-	Mon	Toton	Toton	Toton
0186-14	Nigel Friend	Safety-Kleen	Sandquay	22/04/14	22/04/14	Tue	-	Tue		Sandquay
0521-14	Nigel Friend	Addicott Electrics	Dorchester	22/04/14	22/04/14	Tue	-	Tue		Dorchester
0658-14	Darren Smith	Interflex Scotland	Not specified	22/04/14	22/04/14	Tue	-	Tue		Not specified

<colgroup><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>