Using conditional formatting in table for Pivot Table filtering ...

adambc

Board Regular
Joined
Jan 13, 2020
Messages
192
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have a table of assets with a Date Purchased column and an Age column using =DATEDIF(C3,TODAY(),"Y")&" Years, "&DATEDIF(C3,TODAY(),"YM")&" Months" - there are also Location and Category (of asset) columns ...

The Age column then has conditional formatting that fills the cell green/amber/red depending on the age of the asset, which supports filtering by fill colour/simple way to get at a list of older assets ...

I now want to create a view of assets by Location, by Category - so far so good in a (simple!) Pivot table - but I then want it by Age (Band) ie the conditional formatting colours but I can't find a way to do this in a Pivot Table ...

Is it possible, or am I going to have to revert to another approach?

Thanks ...
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,838
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Well, CF is based on a condition - use the condition.
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
192
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Well, CF is based on a condition - use the condition.
I get that conceptional, but not sure how to implement?

Once I've changed Purchase Date into Age in the format "x Years y Months", I then break the many combinations of that into 3 groups green/amber/red using a set of conditions (as you assert) ...

But in the Pivot Table, I want per Location (row), Category (of asset) sub divided into my green/amber/red groups, not the many combinations of x Years y Months ...

Does that make sense?

PS; can't post my Workbook because it contains confidential information - but if necessary I can create a copy that I can massage with some dummy values?

Thanks again ...
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,838
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Can you add a column to your data that says green/amber/red - this may use the same formula as the condition for CF?
Then include this in your pivot table. On which you can also apply CF if necessary, although that's a bit tricky if I recall correctly.
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
192
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Can you add a column to your data that says green/amber/red - this may use the same formula as the condition for CF?
Then include this in your pivot table. On which you can also apply CF if necessary, although that's a bit tricky if I recall correctly.
The problem is, the source file comes from "Head Office" and I'm not allowed to alter it ie add a helper column!!!

Here's the set up ...

Column B / Location uses a drop down list (no manual input allowed)

Column B / Category (of Asset) uses a drop down list (no manual input allowed)

Column C / Purchase_Date in the format dd/mm/yyyy

Column D / Age is created by =DATEDIF(Cn,TODAY(),"Y")&" Years, "&DATEDIF(Cn,TODAY(),"YM")&" Months" (in the format "x Years y Months")

Conditional Formatting of Column D is ...

=DATEDIF(Cn,TODAY(),"M")>54 = RED fill
=DATEDIF(Cn,TODAY(),"M")>36 = AMBER fill
=DATEDIF(Cn,TODAY(),"M")<37 = GREEN fill

If I was allowed to add a helper column, it would be Column E / Age_Group ie RED, AMBER or GREEN (using alternative labels that are more meaningful!

I'm not even sure whether it's possible, but when I try to use the CF formulas in a nested IF statement in a Pivot Table Calculated Field, it seems DATEDIF cannot be used!

What I'm looking for is two Pivot Tables ...

Category as ROW
Age_Group as primary COLUMN
Location as secondary COLUMN
Count of Category as VALUE

Location as ROW
Category as primary COLUMN
Age_Group as secondary COLUMN
Count of Category as VALUE

I guess the first question is "is this even possible without a helper column?" ...

... and if the answer is yes ...

"how?"!!!

Thanks for any help anyone can give me ...
 
Last edited:

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,838
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
I was about to suggest to use EDATE function in a PivotTable calculated field to determine the age group.
Unfortunately there are Functions you cannot use - database functions and ones that return variable results (RAND, TODAY, NOW). So the problem is probably not DATEDIF.
You also cannot use cell references in Calculated Fields.
So - helper column. If you can't include, convince them to include it.
The other option is to create a separate sheet/workbook - recreate or link the data and add a helper column, then make the pivot.
 

adambc

Board Regular
Joined
Jan 13, 2020
Messages
192
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Of course, it’s TODAY that’s the problem isn’t it?!

I wonder - can’t test it until next week - if I added a “hidden” cell that =TODAY, then use DATEDIF(Cn, {hidden cell}, “M”) etc etc, would that work?

I’ll try it and let you know?

If not will have to “re-negotiate” a helper column!!!

Thanks again …
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,838
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
If you read my previous post: you cannot use cell references in pt calculated field.
 

Forum statistics

Threads
1,141,605
Messages
5,707,355
Members
421,503
Latest member
Rickys03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top