Hi I am trying to use index/match function as explained in this thread but quite not get it working for my situation.
Problem: I am required to produce a weekly report to check the variances against the working day 1 forecast with weekly actuals.
Sheet 1: Working Day 1 Forecast:
On day 1 of every month, I receive a report having the following information
Month, Programme ID, Project ID, Resource Name,Task Name, planned hours, planned cost.
Sheet 2: Weekly Actuals:
Every Friday, I receive the weekly actuals with the following information.
Month, Programme ID, Project ID, Resource Name, Task Name, Actual Hours, Actual Cost.
Sheet 3: Weekly MI Report:
The report I produce weekly have the following columns
Month, Programme ID, Project ID, Resource Name, Task Name, WD1 Planned Hours, WD1 Planned cost, Actual hours, Actual Cost, var in hours, var in cost.
For this report, The WD1 planned hours and WD planned cost in Sheet 3 need to be populated weekly basis by comparing the first five columns in Sheet 1 with the first five columns in Sheet 3. The rest of the data I just populate from Sheet 2 except for Variances which are calculated fields.
The no. of records in Sheet 3 are always greater than the no.of records in Sheet 1 because resources who are not included in the day1 forecast will be booking time to project and their actuals will be coming through. I want to populate 0.00 against these new resources planned cost and planned hours.
Also, the volume of rows at the beginning of the year is 6000 and it keeps increasing every week and by end of the year it can reach about 10000.
Any help is greatly appreciated.
