Excel 2007 : Creating a Team Calendar that Automatically plots holidays, and meetings

[Willen]

Hi, I have been spending some time watching Excel videos (Youtube) and playing around with dates and plotting gantt charts in Excel 2007 (2010 for work). I have succesfully created a perpetual calendar and have formatted it with each month separated (goes across columns rather than down rows) and have a number of team members below (columns) for each month. What i want to be able to do is use a data entry screen where i capture holidays and team meetings and automatically plot this on to the calendar view. I was able to do this when creating a Gantt but it only looked at the single date in the same row whereas this will have to look at all the dates on the data entry sheet against a row in each month.

I probably haven't described this very well and have included three attachments to visually represent this;
1. data Entry sheet - shows how i am entering the holiday dates now
2. Old Manual Plotting View - shows how i then manually plot holidays and meetings (its a pain!!)
3. Perpetual Calendar view - how the calendar now looks and what i want to plot into

I use the "=IF(AND(L$4>=$E12,L$4<=$G12),1,"")" formula, with conditional formatting, to display a blue box in an Excel Gantt. this works fine if the plotting date is in a single line and i'm unsure how to make this work.

Any help or guidance you can provide will be greatly received.

Thanks