Good morning!
Okay - I have attached a screenshot of a report template that I have set up. There are two component tables: the bottom table contains details of projects by name and also by an objective code (e.g. RP01 S1, RP01 S2 etc). These relate to short, medium and long term objectives (S1, M1, L1 etc) and the prefix RP01 is the Workstream within which that project and objective are based.
The Status column records a number 1, 2, or 3 which sets a coloured flag - GREEN is on track, AMBER is off track but recoverable and RED is off track and in trouble. What I want to do is for the numbers for each OBJECTIVE to be added together to calculate a score for each Workstream. This is because a summary of the status of each Workstream is to be displayed in the top table. I want conditional rules to look for each OBJECTIVE in the objective column AND the score given for its status and combine them in some sort of pivot table. The total for each Workstream can then be set the same conditional rules and coloured flag to suggest an overall risk status (GREEN on track, AMBER off track but recoverable etc) for the top table. It will be up to managers, however to combine this with their own knowledge and make a decision as to the actual risk.
To clarify, the RP prefix in the Objective column relates to the Workstream (WS1, WS2 etc) in the top table.
So - how to make a pivot table that looks up the data I want and can display each objective and the total scores based on the STATUS in the bottom table of the main worksheet?
Any advice gratefully received!
ExcelForumQuestion2.png
Bookmarks