+ Reply to Thread
Results 1 to 25 of 25

Multi Sheet pivot table

  1. #1
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Multi Sheet pivot table

    Hello everyone

    i have 8 sheet (ZSC, MBZ, NAH, MAK, RAS, HNZ, KBZ, SHA) that are exactly the same but for different venues

    Each sheet has 9 subjects, ( each subject will have different number of row under them depending on teh sheet)
    • MATV
    • CCTV
    • TV Lighting
    • Audio
    • Comms
    • SIM Trans
    • HAwk Eye
    • Pitch Side LED
    • Power


    i want to create a pivot table that will allow me to view the combine data from the 8 sheet according to the 9 subjects.

    i have attached the excel file that i am working on at the moment for reference
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Multi Sheet pivot table

    The sample file does not appear to show what you want. You need to help us to help you by manually mocking up what you are looking for.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    Please find attached the revised file. I made the MATV sheet as the final output
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    hey guys any idea if this is even possible to achieve??

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Multi Sheet pivot table

    Basically PivotTable is to analyze data not for changing layout
    btw. update your profile about your real excel version

    here is consolidation via PivotTable
    Attached Files Attached Files
    Last edited by sandy666; 02-14-2018 at 01:26 AM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    Dear Sandy,

    that i was able to make, but on the consolidated pivot table i love the order of each section.

    i guess this is not possible to achieve.

    thank you for giving it a shot

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    PivotTble works with columns and single headers row
    If you need use section (name of the sheet) you need to redefine your "tables" and add section name to each one. You can try add section name as column.

    I see your Excel version updated.... give me a few....

    Tables (Excel Tables) don't like blank rows, merged cells and some more.
    I think you should create brand new workbook with corrected tables (and some data in it)
    Last edited by sandy666; 02-14-2018 at 01:55 AM.

  8. #8
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    the blank rows can be removed that's not an issue, same with the merged cells.

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    So prepare proper example, add Section column to each "table" like ZSC, etc... (after all you can filter by Section) and some numerical data because without data it doesn't make sense
    and try again
    Last edited by sandy666; 02-14-2018 at 02:11 AM.

  10. #10
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    please see attached an example as requested
    Attached Files Attached Files

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Multi Sheet pivot table

    ok, see here.
    Done with PowerQuery (Get&Transform) & PivotTable
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    hey Sandy,
    thank you very much for this.

    one question, in Venue C there is an Item Called Earth Rods, but it doesn't show in the sheet 2 that you made, any reason for that?

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    Ouch!!! wait a moment

  14. #14
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Multi Sheet pivot table

    here is second version (sorry about previous)
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    thank you for your help, it's still uses PowerQuery (Get&Transform) & PivotTable correct?

  16. #16
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    i will try and reproduce it on my own file

  17. #17
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    Exactly, you've Ex2016 so it make life easier.

    Again, sorry about previous version, it's happen if I am watching tv, reading news, talking to someone and working on excel - all in the same time

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.

  18. #18
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    hey Sandy,

    i am trying to re-create the file you made me on my file but i am having a small issue.

    the Earth Rods item is not coming out in the Power Query, how do i make it show?

  19. #19
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    select Section: Power - there is Earth Rod, but Section CCTV doesn't have Earth Rod so PivotTable didn't show that
    PivotTable uses PowerQuery from background (example excel file from post#14)

    edit: PowerQuery is used for consolidate tables from different sheets but to show the result PivotTable is used
    Last edited by sandy666; 02-14-2018 at 06:51 AM. Reason: see edit

  20. #20
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    Hey Sandy,

    sorry i feel stupid,

    i followed all the steps and tried to recreate the file, was able to see all the items but now when i make changes in the Qty of the tables it's not updating.

    i attached the file that i created, i am not sure where i am going wrong

    really sorry to bug you with this
    Attached Files Attached Files

  21. #21
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Multi Sheet pivot table

    Much easier to solve if you attach the problem file.

    Marked as unsolved for now.

  22. #22
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    Insert: PivotTable
    • Use an external data source
    • Choose connection
    • select and open: Query - Append1
    • select destination point: new worksheet or existing sheet

    the rest you know how to use (I hope )

    btw. not necessary to load Query to the sheet, if you create Append1 then Close&Load then ONLY CREATE CONNECTION
    Last edited by sandy666; 02-15-2018 at 07:46 AM.

  23. #23
    Forum Contributor
    Join Date
    06-10-2012
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    120

    Re: Multi Sheet pivot table

    that's exactly what i needed, that step was missing

    thank you so much

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: Multi Sheet pivot table

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  25. #25
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Multi Sheet pivot table

    You are welcome and have a nice day

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multi-Table Pivot Incorrect
    By sadele89 in forum Excel General
    Replies: 1
    Last Post: 09-15-2017, 12:26 PM
  2. Multi-sheet Pivot table help
    By FearDavid0586 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-30-2017, 10:19 AM
  3. Writing the loop for updating multi-table MS access file using multi-sheet excel
    By relabz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2016, 03:10 PM
  4. Values on first row of multi row pivot table
    By jorad in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-05-2012, 07:31 AM
  5. Pivot table using multi-value column
    By inomata in forum Excel General
    Replies: 1
    Last Post: 09-30-2010, 03:46 AM
  6. Multi-Sheet, Multi-Table Vlookup possible?
    By cte in forum Excel General
    Replies: 6
    Last Post: 09-11-2010, 11:18 AM
  7. [SOLVED] Multi-Sheet Pivot Table Too Large
    By Jack in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 11:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1