Conditional Formatting using EPMFormat sheet

[Former Member]

hi Team,

Is there a way to have conditional formatting works using EPMFormatting sheet? For Eg. I have below EPM report requirement where I have show Actual, Plan and variance in the report and I have to apply color code on Plan based on variance.

If the variance is negative then Plan should be highlighted with Red color

if the variance is positive then Plan should be hightlighted with Green color

if the variance is close to zero then highlight with Amber.

I have tried using conditional formatting in the EPMFormat sheet but it does not reflect the colors as desired. Please note that if I apply conditional formatting on Variance column based on variance values it works but if I apply conditional formatting on Plan column based on on Variance values conditional formatting does not work.. I have been playing around with EPM formatting sheet alot from last few days but in vain. Any help is highly appreciated.

Thanks

Vj

[Former Member · Accepted solution]

Hi Vijay,

which version of Excel do you use? I am on 2010 and have a similar scenario. I have two columns (column U and T) which both show either a 0 or 1 value based on an if statement. These two are hidden but a third column (column Z) then turns either green, yellow or red depending on whether the sum in the two hidden columns is 0, 1 or 2.

The conditional formatting uses a formula and the formula is =($U18+$T18)=1 => cells turns yellow. And then two more conditions for =($U18+$T18)=0 => green and =($U18+$T18)=2 => red.

You can simply enter this in the EPM Sheet as the condition, the important bit is that you use the $ signs to hardcode the columns and the row number for the row in the formatting sheet. In my report row 18 is the first data row but in the formatting sheet the definition is made in row 64, so in the formatting sheet I have used row 64 instead of 18.

Hope this helps,

Arnold

[Former Member · Accepted solution]

Arnold,

Its works.

But quick question. How it works when we have Actual Plan Variance columns repeating for multiple time periods?

Thanks,

[Former Member · Accepted solution]

Hi Vijay,

You would have to work with the $ to fix the columns and therefore define it on the formatting sheet in relation to the cell you are working in. Essentially treat the columns as you do treat the rows right now.

The variance is always shown on the right of the plan data, so on your formatting sheet instead of using $U you would use the G. As the formatting sheet should always have the same columns, your data format should be defined in column F, so the next column would be G. This should then translate into a format on the report that references the cell to the right of the formatted cell.

BR,

Arnold