Pivot Table by Multiple Consolidation

[Microsoft Forum]

Hi all,

I have a problem of creating pivot tables from multiplie consolidated
ranges.

The source comprises of 12 ranges, each of them is placed in a separate
worksheets, named from "Jan" to "Dec".

The format for each worksheet is the same:

CATEGORY PRODUCTS SALES
cat1 pro1 xxx
cat3 pro2 xxx
cat2 pro3 xxx
.. . .
.. . .

although the exact order of labels for the column "CATEGORY" and "PRODUCTS"
is not the same, of course.

The pivot table created thereafter is not what I expected, what I expected
is

pro1 pro2 pro3
cat1
cat2
cat3

But the actual consolidated table is shown as
products sales
cat1
cat2
cat3

The "products" field contains count of products, and the "sales" field
contains count of sales.

What's the problem? Please advise.

Frederick Chow
Hong Kong.

 

[Frank Kabel]

Hi
where have you put the 'products' field. It should be a column item.
Sum of 'Sales' should be the data item and category your row_item

 

[Microsoft Forum]

The "Products" field is not shown! Rather there is a column item called
"Products" is shown. Sounds strange? By the way, can I post my problem
workbook here?

Frederick
Hong Kong.

 

[Debra Dalgleish]

Pivot tables are limited when created from multiple consolidation
ranges. There's an example here:

http://www.contextures.com/xlPivot08.html

You'd have the most flexibility in your report if you store all the data
in the same worksheet, with Category, Products, Sales, and Date (or
Month) as column headings. Then create the pivot table from the combined
data.