 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004Pivot Table by Multiple Consolidation
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. 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  Signature-- Regards Frank Kabel Frankfurt, Germany > Hi all, > [quoted text clipped - 37 lines] > Frederick Chow > Hong Kong. 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. > Hi > where have you put the 'products' field. It should be a column item. [quoted text clipped - 50 lines] >> Frederick Chow >> Hong Kong. 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. > Hi all, > [quoted text clipped - 37 lines] > Frederick Chow > Hong Kong. SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.