Conditional Formatting based on values from a Pivot

[Rajula]

Hi,

I have applied this formula =AND($C6="Other",$E6=1) for conditional formatting. c6 & E6 are part of a Pivot Table. The Conditional formatting formula is applied in a table outside the pivot. But this doesnt seem to be working. Appreciate any help.

Thanks & regards

[Special-K]

Prob best post your spreadsheet, otherwise it looks ok

Debug it by breaking the formula down into

=($c6="Other")

and then try

=($E6=1)

to see which condition is failing

[Rajula]

Hi,

Thanks for your response. I have attached the file. Please take a look

[kbaruin]

Hi Rajula

Is it the True/False values you are looking for in the Months columns?? Are you using the conditional formating formula in the months columns?? on your attachment I can see your comment showing a cell under Dec. The formula perfectly works and gives a value False.

Can you explain a bit more about what you are looking for.

Thanks

[Rajula]

Hi Kbaruin,

I want the conditional formating in all cels starting S6 to AD15. If Type is Other and if the Cell Value is equal to "E", it should highlight the cell in RED. This is what i want the conditional formatting to do.

Thanks & regards

[kbaruin]

select the cells from S6-AD15 and apply conditional formatting, add rule and select the last option. THe formula should be =AND($C6="Other",$E6=S6) and apply to $S$6-$AD$15. See attached.

[Rajula]

Thanks for the response. The formula is not working dynamically. The conditional formatting for Row 7 should be validated for C7 & E7, Row 8 for C8 & E8 and so on..

[kbaruin]

As far as I can see, it works perfectly fine as you asked. The cell color in the months columns(i.e S6 - AD15) cahnges to RED if C$6 = "Other" and the cell value = value in E.
I tried changing the cell values in few cells making the condition true and it shows RED. See attached.

[Rajula]

Hi Kbaruin,

Thanks for the responses..
When i go to cell T15 for example and check the conditional formatting formula it shows =AND($C6="Other",$E6=S6)
Shouldn't this be changing to =AND($C15="Other",$E15=T15) ?

Thanks & regards
Rajula

[kbaruin]

The formula is not just for one cell, it is applicable for all cells from S6 - AD15. if you want the conditional formatting formula to be shown as you asked, you need to creat a conditional formatting rule to each cell which is not recommended. Whatever the formula shows, you got the functionality you are after on the sheet which dynamically changes as the values change, right.