Creating Resource Capacity Planning Per Project

I am trying to create a resource capacity planning per project and I have some issues with some functions. Here is what I did :

1/ I have a workbook for each project with 4 sheets : "Project", "Pivot", "Project Identification", "Capacity"

2/ On the Project sheet I have the volume of man days for resources (rows, can be multiple according to the resource skill) per project phases (columns, always Gate 1 to 6).

3/ On the second sheet I have the pivot table of the first sheet which sum up the volume of man days for each resources per project phases.

4/ On the third sheet I have a project identification table with the columns Date, Week Number, Duration (Weeks), Phase (Gate 1 to Gate 6, so 6 rows). I required the user to enter the date of each Gate, the week number and duration are calculated.

5/ On the last sheet I have my capacity planning with:

- the number of columns is always 52 corresponding to a full year, but can start in the middle of the year, for example start week 30 to end week 29.
- the first row is the weeks numbers, the first cell is the week number of the Gate 1 date, the following cells are the previous cell +1 ( fx=IF(B1>51;1;B1+1) )
- the second row is the phase we are in at that week ( fx=VLOOKUP(B1;'Project Identification'!$B$2:$D$8;3;TRUE) )
- the following rows are the man days for each resources (1 row per resource), this is only the volume of man days for the phase identified in the sheet "Project" divided per the number of weeks for this phase. ( fx=IF(Pivot!$A2="";"";IF(C$2="";"";HLOOKUP(B$2;Pivot!$B$1:$G$4;ROW()-1;FALSE)/VLOOKUP(B$1;'Project Identification'!$B$2:$C$8;2;TRUE))) )

This is working ok if the project execution is within a year (for example from week 20 to week 41). But if the project is over 2 years (for example from week 41 year n to week 12 year n+1) it does not work at year changing.

For example if I have Gate 3 on 19/08/2013, Gate 4 on 30/11/2013, Gate 5 on 29/01/2014 and Gate 6 on 12/02/2014 then on the "Capacity" sheet for week 34 I will have Gate 3 but on week 35 it will switch directly to Gate 6 up to week 52 and then at week 1 I will have "#N/A".