Hello I can see others have posted about this previously, in separate forums but can't wrap my head round it with their solutions etc.
I do apologise if I frustrate you, but will appreciate any help in advance.
I have a pivot table that pulls data in from calls closed on and split out via their retrospective regions (departments). I then have it reporting across, the totals they have achieved in the months. For example, in the month February "Lightning - PFIs North" have achieved 10 calls closed.
Screenshot 2022-03-16 154711.png
In red and outside of the pivot table are some targets I would like to reference against. so again for Lightning - PFIs North The target I need to achieve is 26 but I only achieved 10 in February. I would like it so that if it was 25% or less that its target it will be Red, 26% to 74% Amber and anything else Green. I would like it so that the conditional formatting applies to the pivot table so when users review the spreadsheet and update the data, they do not need to apply conditional formatting themselves, it just refreshes based on the refresh of the pivot table.
The problem that I run into and I am hoping you can help come to a solution for me is: Pivot tables do not allow for relative references. So when I try to suggest B5<$F5 it throws its toys out the pram. I do not wish to go through each cell and reference a new conditional formatting every time. I would like it so that it does it in a way that when I come back in the future and Apr/Jun/Jul start to be filled in, it auto-formats it and the same with any new regions (departments) added.
I am forever grateful for any help.
Many Thanks
Bookmarks