
Active Member

June 25, 2020

Hi All,
Hope you all are safe & fine. I am working on a excel sheet as a Progress tracker for multiple products manufactured at different sites and i need to track the activity related to gathering of data for a Risk assessment exercise.
I am able to extract a pivot table which serves my purpose. However i need some help to see whether the pivot table data could be enhanced by adding few more automated steps which i have mentioned in the file attached. (There are 2 tabs, RA Tracker & Summary (where i have posted the requirements)
Any help or guidance is greatly appreciated.
Thanks,
Mahesh


July 16, 2010

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

Active Member

June 25, 2020

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


July 16, 2010

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

VIP

Trusted Members

June 25, 2016

Hi Mahesh
You can check this article to see if it helps somewhat. Don't think it will work on PivotTable though.
https://www.myonlinetraininghu.....ored-cells
Sunny
1 Guest(s)
