Hello all,

I need to combine schedule dates of start and finish with cost of work being carried out in 127 establishment’s projects. For reasons I won’t go into here producing this directly from project plans or their parent programme plan is not possible!
The schedule information is exported directly from the programme plan to an excel spreadsheet and, with each project having more than one stage, each establishments can appear more than once in the exported data table.

By arrangement the stages of any plan do not overlap i.e. the earliest stage 2 will start is the day following stage 1 finish.

The time scale covered is 5 years and of necessity I need to produce a day by day account of anticipated costs which means that with Excel 2003 the dates field has to be in a column with the establishments shown in a row.

The financial information is simply produced from cost estimate divided by a given number of days work duration and entered above each establishment name in the TimePhased worksheet.

The images below hopefully show enough of the content of the sheets to allow you to understand my requirement.
Even though I have learned a lot from the forum in the past I have tied myself in knots trying to come up with a way to do the following, :-
There are two worksheets "TimePhased" and "Schedule"
TimePhased.jpgSchedule.jpg

In the TimePhased worksheet, for each Date in column B, I need to identify if that date is between the Forecast_Start and Forecast_Finish dates of the first establishment listed on the Schedule worksheet and if it is copy the 'Day Rate' held in Row 1 into the cell that is the intersection between the subject Date row and the subject Establishment column.

When done repeat with next establishment listed in the schedule worksheet - which may be the same establishment again but with different Start and Finish dates.

Although I have put many hours into this I have got nowhere. I have no code that I can show you to ask where I am making mistakes as all my attempts fail because there can be two or more entries in the schedule table of the same establishment.

I hope my explanation is clear enough and someone can see through my problem and provide a solution or make suggestions on the correct approach to take.

Thanks