Sep 21 2022 01:53 AM
Good morning everyone,
I am currently stuck on something that I am not even sure is doable...
I would like to get to highlight values within the pivot table depending on fields, my table is as followed:
Raw data is a split per customer of the purchases per region so 14 different lines per customers. But we want to check specific regions so there's Exports America and Rest of the world focus. Either those lines are filled with a number that a formula consider as "Updated" or with a dash considered as "Empty". What I want to do is highlight in the pivot table per customer if "Empty" for Exports America equals 12 and if "Empty" Rest of the world equals 2. I tried some conditional formatting but it applies to the whole table and if Exports America = 2 then it's highlighted when it's in fact normal.
Is there anyway I can make two specific conditions ?
Thanks in advance for your help!
Sep 21 2022 03:19 AM
Conditional formatting in a pivot table is tricky since the range of the pivot table can change when you filter or update the pivot table.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Sep 21 2022 06:20 AM
I'm very sorry, it says I can load files or link... I don't know what's happening. The best I can do for know is this !
This is what the TCD looks like and this is below the raw data! Hope it can helps further
Row Labels | Count of forecast |
Customer 1 | |
Exports Americas | 11 |
Rest of the world | 2 |
Customer 2 |
|
Exports Americas | 11 |
Rest of the world | 2
|
Account Name | Year | Scope | Trade Orion | W39 | W40 | W41 | W42 | W43 | W44 | W45 | W46 | W47 | W48 | W49 | W50 | W51 | W52 | forecast check |
Customer 1 | Current Year | Rest of the world | WORLDWIDE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | US | - | - | - | - | - | - | - | - | - | - | - | -
| - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | CANADA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | MEXICO | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | BRAZIL | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | ARGENTINA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Current Year | Exports Americas | CHILE | 250 | 190 | 190 | 200 | 200 | 225 | 225 | 230 | 235 | 235 | 235 | 235 | 235 | 230 | UPDATED |
Customer 1 | Last Year | Rest of the world | WORLDWIDE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | US | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | CANADA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | MEXICO | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | BRAZIL | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | ARGENTINA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 1 | Last Year | Exports Americas | CHILE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Rest of the world | WORLDWIDE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | US | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | CANADA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | MEXICO | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | BRAZIL | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | ARGENTINA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Current Year | Exports Americas | CHILE | 3600 | 3600 | 3600 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | 3400 | UPDATED |
Customer 2 | Last Year | Rest of the world | WORLDWIDE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | US | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | CANADA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | MEXICO | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | BRAZIL | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | ARGENTINA | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Customer 2 | Last Year | Exports Americas | CHILE | - | - | - | - | - | - | - | - | - | - | - | - | - | - | EMPTY |
Sep 21 2022 02:01 PM
Perhaps like this?
Sep 21 2022 11:42 PM
Sep 22 2022 12:34 AM
This way?
Sep 22 2022 07:40 AM
Sep 22 2022 08:07 AM
I highlighted the value of EMPTY instead of the customer.
If this is not what you want, please explain in detail what you do want.
Sep 22 2022 11:28 PM
Sep 23 2022 03:16 AM
You originally asked to highlight 12 EMPTY for Exports America; that's what the rule currently does (but since the number is 11, Exports America isn't highlighted). You can easily change the rule to highlight if the number is 14 instead of 12. The formula is now
=OR(AND($A4="Exports Americas",$B4=12),AND($A4="Rest of the world",$B4=2))
If you want 14, change it to
=OR(AND($A4="Exports Americas",$B4=14),AND($A4="Rest of the world",$B4=2))