Results 1 to 8 of 8

Thread: Multi-sheet Pivot Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multi-sheet Pivot Table

    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

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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.

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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

  4. #4
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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.

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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

  6. #6
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

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

  7. #7
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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

  8. #8
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multi-sheet Pivot Table

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •