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

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

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

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

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

please see attached an example as requested

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

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?

Ouch!!! wait a moment

here is second version (sorry about previous)

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

i will try and reproduce it on my own file

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.

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?

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

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

thank you so much

You are welcome and have a nice day