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

[adambc]

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 ...

 

[bobsan42]

Well, CF is based on a condition - use the condition.

 

[adambc]

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]

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]

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 ...

 

[bobsan42]

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]

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]

If you read my previous post: you cannot use cell references in pt calculated field.