Hi there,

I am trying to create a calendar in Sheet1 which identifies allocated time from consultants' business plans stored in their respective worksheets 2-4. Each consultant has 3 business plans. Each business plan shares the same template, containing 20 standard activities. Consultants will only ever take on a maximum of 8 activities per business plan per year but these activities will vary between each of their 3 business plans.

In Sheet1 (Calendar):

B2:I2, J2:Q2, R2:Y2 = Consultants 1-3 with activities 1-8 (sheets 2-4)
A2:A367 = 01-Jan-08 - 31-Dec-08

In Sheet 2 (Consultant1 Business Plan):

A2:A20 = activities
C2:C20 = start date
D2:D20 = finish date

A21:A41 = activities
C21:C41 = start date
D21:D41 = finish date

A42:A63 = activities
C42:C63 = start date
D42:D63 = finish date


If Consultant1 is starting an activity on 01-Jan-08 and finishing on 14-Jan-08 I want this information to be pulled accross to the calendar so that B2-B15 shows the activity being worked on. PLEASE NOTE that in the business plan I only have a start and finish date. In the calendar I want the period from the start and finish date to be populated.

The calendar allows for 8 activities per date i.e. each consultant has 8 columns. I want the the calendar to always populate the left most column available for a particular period. E.g. if Consultant1 starts and finishes 1 activity between 01-Jan-08 and 14-Jan-08 and starts and finishes another activity between 12-Jan-08 and 31-Jan-08 the first would pull accross to B2-B15 and the 2nd would pull accross to C12-C32.

Hope you can help?!

Many thanks