PMO Portfolio Dashboard Report - Need help

[FJafar]

Hello folks,

I am hoping someone out there in the world can help me build PMO Dashboard.

I have attached the draft and 5 questions related to the dashboard. I would very much appreciate your help.

Regards

 

[vletm]

@FJafar - the 1st version
I tested with basic values [E..H] , no connection with [I..L]
Portfolio 1 with all possibilities.
Portfolio 2 with all 'green'
Portfolio 3 with one 'amber'
Portfolio 4 with at least one 'red'
PMO Dasboard is 'the result of previous'
E-column is still empty!
J-column has TREND of [F:I] and K-column has it's sparkline; TREND most right.
>> Something like this?

 

[FJafar]

Hi vletm, thank you for a quick response to my request.

Yes, more or less like this.

I tested the values and while RED and GREEN calculation is working but for AMBER if there are TWO AMBER it turns to RED. Is there a way to keep it AMBER even if all FOUR are AMBER?

Trend is good but graph is very small and I couldn't increase the size or move it to another area. How can I change the size?

Regards

 

[vletm]

You asked:
If any ONE of the four cells is AMBER and other THREE are GREEN Then D6 is AMBER
It should work like that...
Do You want to change it?
Those 'graph' are 'sparklines'
I made one more sample - ver 2.

 

[FJafar]

Hey vletm,
Whats happening is that;

If I change TWO or more cells to AMBER then the overall changes to RED.

What I want is if all Four cells are AMBER then Overall should turn to AMBER as well.
See ROW #7 & 8 in attached file.

Thanks for the graph!.

 

[vletm]

@FJafar
Portfolio 1..4 has changed as 4*AMBER gives AMBER
If more changes, You should write table of every possibilities.

 

[FJafar]

Thanks for your help vletm, much appreciated!

I have updated the file with 5 sets for rules for overall RAG and also included a table to give you an idea of possibilities. However, if you follow the rules that would be lot easier.

I am not even sure if I was able capture all the possibilities due to large number of combination.

Regards

 

[vletm]

@FJafar
I followed rules!
You changed some of those!
Now, You maybe change all rules ...?
How to follow rules, if rules are not fixed?
Do You want to use ONLY BACKGROUND colors for RULES or cells values?
If color base rules then no conditional formatting or
conditional formatting is okay, but needs values in cells.

 

[FJafar]

Hi vletm, rules are same from the start, I just provided additional explanation.

Anyway, I prefer to use BACKGROUND colours for RULES if possible.

Thank you!

 

[vletm]

@FJafar
Different rules ... okay.
Still one question, Do You will manually set every background color, really?
... and just those colors of course?

 

[FJafar]

Yes, I'll manually set the background colours.
And only these THREE colours.

regards

 

[vletm]

@FJafar
Read Cell[C2]'s instructions!

 

[FJafar]

not sure what you mean by that? Which sheet are you referring to?
What instructions do you want me to follow?

 

[vletm]

@FJafar
ReRead Your #7 Reply: You asked to make to that 'Sheet2'!
Activate 'Sheet2' and Focus to Cell [C2]!
There are three words.
= Move Mouse over [C3:C4] and make 'left click' (with right hand mouse) !
After click, You will get that You wanted!

 

[FJafar]

Thank you vletm for your help.