Log In
Register
Home
Active Member
June 25, 2020
Offline
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
Offline
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
Offline
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
July 16, 2010
Offline
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.
Active Member
June 25, 2020
Offline
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
Offline
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
Offline
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)
