Resource Tracker - Formula required

Hi,

I have a project dashboard which I am constantly adding to all the time.

I want to try and add a resource tracker similar to the one here: http://chandoo.org/wp/2009/08/05/exc...ct-management/

However – I am completely stumped with how this can be achieved easily – I do not believe it can be.

What I would like is a formula on the “Resource Loading Chart” which sums the number of tasks a particular person has in a week.

In the chandoo link it was easily achieved as it had a separate sheet listing the tasks by person and the week number it related to so it was a simple sum product formula.

However… I would like the model to automatically work out the tasks in a week. I have no idea how this can be done – is excel cleaver enough to count if a box is shaded? Then I got thinking well the chart is clever enough to know when to shade it so it could put a “1” in the cell also and I could count that. The problem with that is I would then lose the Actual arrow funcationalilty which is essential.

Anyone have any ideas how to achieve this? My preference is to avoid helper columns unless necessary.

I have uploaded a spreadsheet including an example of what I would need the formula to calculate.

Paul

Hi,

Sorry – I should have been clearer. The conditional formatting works already but it’s the underlying data I need it to somehow pull.

On the attached “Resource Loading chart” I have cells G5:AA23 working where it pulls the number of activities each person has to complete in a particular week and shades it accordingly. The data is manually inputted in columns B:E and I wondered if there was a clever way that this could be populated automatically based on the sheet “Gnatt Chart Template”. On this sheet it has the person responsible but what it doesn’t list obviously is the week the tasks needs to be performed.

The arrow functionality is simply if a % is entered in column I then arrows appear as progression. E.g. if marked as 100% then the shaded boxes will be all marked with arrows.

Hi,

This works but then I lost the arrows? Would it require helper columns?

Is there any way a helper sheet could be used which copies one sheet but the main difference being it uses your new formulas so if any need lines were added on the master sheet it would also be added on the helper sheet

Hi,

I have included a helper sheet and your formulas are now on the sheet "Resource Loading Chart" cells G27:AA45

I hate the helper sheet as it means maintaining two septerate sheets and it is killing my machine.

Is there a better way?

Hi,

Really apprechiate your help on this

I still can not get the arrows working - the only time they work is if I change it to 100%

Ace you are a star! I didnt think this would have been possible but you have done it!

Uploaded the model as I am sure someone out there may find a use also!

Now I can have a think of what else I can include on this!