Hi Mahesh,

Welcome to our forum! Thanks for sharing your file.

For question 1 on conditional formatting, please see this tutorial on Conditional Formatting in PivotTables. And this tutorial on Conditional Formatting with Formulas.

In my attachment you'll see solutions to your questions 2 and 3.

In regards to question 4, you can't count colours (easily, without VBA), so I would find another way to count supplier status. You'll have to overcome this for the Conditional Formatting anyway.

To remove the word (blank) simply select one of the cells containing (blank) and press the space bar.

Mynda

Thanks Mynda,

This is good. And I agree that count of colors is not easy. I will anticipate your feedback, if incase you are able to provide some way to deal with this.

Thanks,

Mahesh

The way to deal with it is to use actual data in the cells that can be used in logical tests in formulas. e.g. if = "OK" then green, if = "Pending" then red etc.

Yes Mynda, I also initially evaluated this approach with logical tests as you suggested. But then how would you get the supplier names for the colors (i.e. status with RAG -Red, Amber, Green)? This step is very important because once you extract this data; then we have to follow up with the suppliers (& hence names of suppliers are must for reference) 

Do you think this is doable with any other approach? Let me know please.

Thanks,

Mahesh

It's not doable with another approach unless you're willing and able to write VBA to do it, which will still require logic that you don't have the data to support.

I recommend you put the supplier information in another column and have separate status columns that are coloured.

Mynda

Hi Mahesh

You can check this article to see if it helps somewhat. Don't think it will work on PivotTable though.

https://www.myonlinetraininghub.com/count-sum-and-average-colored-cells

Sunny