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

**scotthunter3** · 04-26-2014, 02:26 AM

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".

Thanks

**tuph** · 04-28-2014, 02:28 AM

Please attach a copy of your workbook so we can don't have to try to replicate what you have already done. Remember to remove/replace any sensitive data before uploading.