+ Reply to Thread
Results 1 to 3 of 3

Pivot Table Conditional Formatting Workaround

  1. #1
    Registered User
    Join Date
    04-15-2020
    Location
    Plymouth
    MS-Off Ver
    2010
    Posts
    19

    Pivot Table Conditional Formatting Workaround

    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
    Attached Images Attached Images
    Last edited by jamesdrizzy; 03-16-2022 at 11:47 AM.

  2. #2
    Registered User
    Join Date
    04-15-2020
    Location
    Plymouth
    MS-Off Ver
    2010
    Posts
    19

    Re: Pivot Table Conditional Formatting Workaround

    Can anyone help with this please?

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Pivot Table Conditional Formatting Workaround

    Not sure that this is doable, however if so I feel that we need a sample file with source data and the pivot table shown in post #1 in order to help.
    Information on attaching an Excel file to your next post is given in the banner at the top of the page.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  2. Replies: 9
    Last Post: 03-07-2016, 10:39 AM
  3. Replies: 2
    Last Post: 09-04-2015, 02:47 AM
  4. [SOLVED] Pivot table functions disabled when workseet is protected. Workaround? VBA to sort?
    By moosetales in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-21-2014, 09:29 AM
  5. [SOLVED] Workaround to Conditional Formatting based on a Format of another cell
    By sarkman22 in forum Excel General
    Replies: 5
    Last Post: 05-10-2013, 01:24 PM
  6. ERROR Handling on Pivot Table with Combo box workaround
    By hitxrafa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2012, 06:06 AM
  7. Replies: 1
    Last Post: 02-08-2012, 09:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1