Summary of mutiple projects/resources

[pauldaddyadams]

Hi,

I have been designing a project model over the last few days but now there is a requirement to have a model which pulls multiple projects.

The way the model will be set up will be that on the sheet “Manual Resource Loading” the planner will input:
The Project ID
The Person working on it
The days allocated
Which week to allocate the days to

Then (hopefully) the “Gantt Chart Template” sheet will pull all this data together automatically.

I need some help with a couple of the formulas:

Formula 1 – Yellow cells.
Start Date – Is there any way a formula can match the project ID and Person on the “Gantt Chart Template” with the data on the on select the “Manual Resource Loading” sheet and select the minimum week number – this would give the start date.

Duration – similar to the above but I would need it to work out max week number. Then I need it to deduct the min number so that it only gives duration.


Formula 2 – Orange Cells
Now in my last model I was determined to keep the arrows (when a % is added in column J it would show progress arrows). This feature is no longer needed and wondered if this formula:

=IF(AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<=CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)),IF(CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)*$J9>K$8,$S$6,AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9))),"")

Could be adapted in such a way where it would show the total days planned from the sheet “Manual Resource Loading” – it would also have to match the project ID, Person and Week number.

I know that’s a lot I am asking for so if anyone can help with any of it then it would be appreciated.

I have uploaded the new model.

Regards

Paul

[pauldaddyadams]

A Quick update - I have managed to complete formula 1 - its just formula 2 I need help with (I honestly do not know where to start).

Does anyone know what part of the formula pulls the arrows in? If I can change this part to a sumproduct formula to sum the hours (by person/week/project) I am there.

[Ace_XL]

So you are getting rid of the arrows eventually

The $S$6 in the formula pulls up the arrows, so without thinking too much, maybe what you want is..

=IF(AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<=CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)),IF(CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)*$J9>K$8,SUMPRODUCT(('Manual Resource Loading'!$D$6:$D$40=$D9)*('Manual Resource Loading'!$E$6:$E$40=$E9)*('Manual Resource Loading'!$G$6:$G$40=K$8)),AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9))),"")

[pauldaddyadams]

Also, I just noted that the sum product formula needs to be amended slightly to sum the number of days, *('Manual Resource Loading'!$F$6:$F$40)

[pauldaddyadams]

Hi

Sadly that hasnt worked - if I past that in to K9 I would expect it to be blue and have a result of 5. Its blue but the sumproduct hasnt worked.

I also note that it looks in column J which is the % completed - it no longer needs to do this. If its blank the cells change colour but if a number is inputted then the colouring no longer works

Any ideas?

[pauldaddyadams]

Hi,

I have been trying to tweek this code for hours with no sucess at all!

[CODE]

=IF(AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<=CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)),IF(CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9)*$J9>K$8,SUMPRODUCT(('Manual Resource Loading'!$D$6:$D$40=$D9)*('Manual Resource Loading'!$E$6:$E$40=$E9)*('Manual Resource Loading'!$G$6:$G$40=K$8)*('Manual Resource Loading'!$F$6:$F$40)),AND(K$8>=CHOOSE(MATCH($O$6,ganttTypes,0),$F9,$H9),K$8<CHOOSE(MATCH($O$6,ganttTypes,0),$F9+$G9,$H9+$I9))),"")

Can anyone help at all?