Thread: Pivot Table Processing

I am using a pivot table to show the amount of product that is being used in
a month. What I would like to do is color code any month that has less than
20 days usage in any month. This is part of a table that I am using:

Product Days Qty Orderld Month
1234 23 12 60 1
2345 26 90 60 1
9586 18 85 60 2
4759 21 65 60 3
2589 12 34 60 4
2589 23 56 60 5
1234 10 89 60 6
1234 23 12 60 1
2345 26 90 60 1
9586 32 85 60 2
4759 26 65 60 5
2589 08 34 60 3
2589 12 16 60 7
1234 18 72 60 8

What I want to do is list the Product by the month and if the product id
less than 10 days I would like to color code the cell.

Jan Feb Mar Apr May Jun Jul Aug Sep Oct
Nov Dec
Product Qty Qty Qty Qty Qty Qty Qty Qty Qty Qty
Qty Qty
Product Qty Qty Qty Qty Qty Qty Qty Qty Qty Qty
Qty Qty
Product Qty Qty Qty Qty Qty Qty Qty Qty Qty Qty
Qty Qty
Product Qty Qty Qty Qty Qty Qty Qty Qty Qty Qty
Qty Qty
Product Qty Qty Qty Qty Qty Qty Qty Qty Qty Qty
Qty Qty

I know that I can use a pivot table for the QTY but I am not sure how to
color code things without showing the days?

Thanks in advance for the help.

Hi, this is not exactly what you intended with the colours but the idea
remains the same. You've got your products separated according to your
key (day<10).

Days10 = IF(days<10,0,1)

Sum of qty mth
days10 prod 1 2 3 4 5 6 7 8 Grand Total
0 2589 34 34
0 Total 34 34
1 1234 24 89 72 185
2345 180 180
2589 34 56 16 106
4759 65 65 130
9586 170 170
1 Total 204 170 65 34 121 89 16 72 771
Grand Total 204 170 99 34 121 89 16 72 805

HTH,
Tomek Polak, http://vba.blog.onet.pl

Tomek,

I am new at this and I don't want to sound slow but where would I place it?

"topola" wrote:

> Hi, this is not exactly what you intended with the colours but the idea
> remains the same. You've got your products separated according to your
> key (day<10).
>
> Days10 = IF(days<10,0,1)
>
> Sum of qty mth
> days10 prod 1 2 3 4 5 6 7 8 Grand Total
> 0 2589 34 34
> 0 Total 34 34
> 1 1234 24 89 72 185
> 2345 180 180
> 2589 34 56 16 106
> 4759 65 65 130
> 9586 170 170
> 1 Total 204 170 65 34 121 89 16 72 771
> Grand Total 204 170 99 34 121 89 16 72 805
>
> HTH,
> Tomek Polak, http://vba.blog.onet.pl
>
>

Daniell, I can not see how far you are so let me start from the very
beginning. For ease of use I would name the range as "Data" for
further references.
1) Select range with your data.
2) Insert > Name > Define > "Data" Add or
just type in the name in the left up window (where "A1" stands) and
press Enter.
Each of the headers in this range must have a unique name.
3) Add column between Days and Qty (Insert > Column)
4) Name it Days10.
5) Select column with Days and name it "days"
6) Right to "23" in Days10 column type =IF(days<10,0,1)
7) Copy that formula for the cells in this colum.
7) Create Pivot Data > Pivot Table... > ... range: type in "Data" with
no apostrophes, create Pivot Table
8) Drag Days10 and Days into Row Field, Months into Column field and
Qty in Data Field.
9) Make sure that you use Sum not Count for Qty unless you don't want
to count the instances of appearance.
10) On Days10 right click and choose Group and Show Detail > Show
Detail
Please let me know where you are.
Tomek

Yes and now I see it. Thanks

"topola" wrote:

> Daniell, I can not see how far you are so let me start from the very
> beginning. For ease of use I would name the range as "Data" for
> further references.
> 1) Select range with your data.
> 2) Insert > Name > Define > "Data" Add or
> just type in the name in the left up window (where "A1" stands) and
> press Enter.
> Each of the headers in this range must have a unique name.
> 3) Add column between Days and Qty (Insert > Column)
> 4) Name it Days10.
> 5) Select column with Days and name it "days"
> 6) Right to "23" in Days10 column type =IF(days<10,0,1)
> 7) Copy that formula for the cells in this colum.
> 7) Create Pivot Data > Pivot Table... > ... range: type in "Data" with
> no apostrophes, create Pivot Table
> 8) Drag Days10 and Days into Row Field, Months into Column field and
> Qty in Data Field.
> 9) Make sure that you use Sum not Count for Qty unless you don't want
> to count the instances of appearance.
> 10) On Days10 right click and choose Group and Show Detail > Show
> Detail
> Please let me know where you are.
> Tomek
>
>