Conditional Formatting Excel Pivot Table

[gseales]

I have a pivot table for customer orders that shows the qty ordered of an item broken down by Category of an item the customer and the date. I would like to be able to show updates of what items on orders have been picked in shipping on this report. I am not sure how to do this in the pivot table. Below would be an example of the pivot table data pivot table layout. I would like to change color of cell for Category1/Item1/Customer4/8-Jan to indicate when "ScheduledQty" is any value other than zero. Is this even possible in a pivot table? Thanks for any help.

OrdNum Item Category Qty ShipDate ScheduledQty
4 Item1 Category1 1 8-Jan 1
2 Item1 Category1 3 13-Jan 0
5 Item1 Category1 6 7-Jan 0
2 Item2 Category1 5 13-Jan 0
2 Item3 Category2 3 13-Jan 0
4 Item4 Category2 1 8-Jan 0

Row Labels 7-Jan 8-Jan 9-Jan 13-Jan
Category1
Item1
Customer4 1
Customer2 3
Customer5 6
Item2
Customer2 5
Category2
Item3
Customer2 3
Item4
Customer4 1

 

[SkipVought]

hi,

What have you tried and what were the results?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gseales]

I'm not sure where to even begin with this because I have the 2 sets of numbers, qty and scheduledqty, but I only show qty. Have not done much with conditional formatting either, and not sure how much more complicated the formatting is in a pivot table. I usually just create reports in MS Access, but found it much easier to use a pivot table for this, because it formats everything nicely. I have used this for years but want to add an indication on to it now when something has been picked for an order. Sometimes an order could have a few items picked for 2-3 weeks ahead of time and it is nice to see what.

 

[gseales]

I added the ScheduledQty to the data section so now it shows both the order qty and the scheduled qty. This would get the job done, but the report does not look as nice. Would like to be able to hide the ScheduledQty Column and use its value to change the color of the Qty cells.

Category
Item1
Customer1 Qty | Scheduled Qty

 

[SkipVought]

Conditional Format (CF)
PivotTable (PT)

Well as you know, when you define a CF for a range of cells, you 1) SELECT the CF Range, 2) the expression you is entered is with respect to the UPPER LEFT-HAND CELL of the CF Range.

However, if your PT ROW FIELDS compliment will vary, then the CF Range UPPER LEFT-HAND CELL will also vary, which will present some challenges.

So if the however is not the case, then simply CF your DATA area. Your CF Range should expand dynamically as more rows/columns are added to your PT via your source data table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]