Multi-sheet Pivot Table

[Audie17]

Hello,

I'm new at this and trying my hardest to create a pivot table that reads multiple worksheets in my file - but I keep hitting brick walls! I'm wondering if anyone is able to help me, pretty please?

My file is for someone who has customers ordering beauty products from her. Each worksheet has columns titled 'No. ' (for the number of times the product was ordered), 'Category' (eg. Face, body, etc), 'Description' (eg. Moisturiser, etc) and 'Price'.

I'd like to know how to create a table that shows, from all customers, the total number of times the product (Description) was ordered, sorted per Category. This is so she can see straight away what's the most popular type of products are being ordered (Category) and within that category the popular products ordered (Description). Would love to show $'s against the categories too, if that's possible.

Hope I haven't confused anyone as much as I am at the moment! Thanks in advance for your amazing help.


Audie17

 

[starl]

If the sheets are the same, you can Consolidate the data.
See: http://www.contextures.com/xlPivot08.html

Basically, you need to use the original Pivot Table wizard which can only be accessed with the shortcut ALT+D. It has an option to consolidate ranges from multiple sheets in a workbook.

 

[Audie17]

Thank you for your help and fantastic link. I’vebeen able to set up my consolidated pivot table, giving each worksheet fieldnames, etc, but unsure how to do the last bit to display the result I want.It's the Pivot Table Fields area on the right-hand side that's confusing me.It's not the same as if I was doing this for a single worksheet, where thecolumn names appear. My columns are as follows:

No. Category Description Price

I would like to see how many products(Description) were ordered per Category, which would be obtained from the No.column - the Price is relevant for indicating total expenditure. Maybe this isn’t possible?


Audie17

 

[starl]

If I understand you correctly, you'll need to change your tables so the Category column is the leftmost column on all the sheets, then recreate your pivot table.

 

[Audie17]

Thank you! This is not what the many internet sites I've visited has told me while trying to figure this problem out. Lesson learned and appreciated.


Audra

 

[starl]

So I did understand you correctly? I wasn't too sure.

 

[Audie17]

You understood perfectly. My pivot table is now functional and makes sense, showing Description (or products) that were ordered per Category and how many of each. Thanks again.


Audie17

 

[starl]

Glad to help!
I just wish Consolidated tables option would just merge the data together into a single table, instead of that summary like setup.