need a pivot table for multiple worksheets

[mark555]

I have a workbook with 11 tabs, one for each floor of a building. Each tab has the same column headers, and I have set each up as a named table (Floor1, Floor2, etc.). Each table consists of equipment part numbers, manufacturer name, room name, quantity, etc. What i am trying to do is get one table that gives a list of part numbers and summed quantity per floor, that also totals for the whole building.
Any help is greatly appreciated!! Sorry if this is a basic problem, but i can't seem to get anything to work right.

 

[rahulshewale1]

hi @mark555,

Please share your file, so i can able help you

you Can achieve result using Power Query and Pivot Table Togetter

 

[Peter Bartholomew]

Taken from Excel help in response to search 'append worksheets'

Automatically append data the easy way
A common problem Excel users have is appending data with the same columns into one large worksheet. For example, you may have an asset tracking solution that started out in Excel but now has grown to include files from many workgroups and departments. This data may be in different worksheets and workbooks, or in text files that are data feeds from other systems. There is no user interface command or easy way to append similar data in Excel.
The best solution is to use Access, where you can easily import and append data into one table by using the Import Spreadsheet Wizard. Furthermore, you can append a lot of data into one table. You can save the import operations, add them as scheduled Microsoft Outlook tasks, and even use macros to automate the process.

I doubt you will want to switch to Access as an environment to build your floor data, so using Power Query to append Excel tables is probably the best option for you. New skills will need to be acquired first though.

 

[Peter Bartholomew]

The following will combine a set of tables provided you are happy to select them manually from the list of data objects in the file

let
    AppendedQuery = Table.Combine({tblFloor1, tblFloor2, tblFloor3})
in
    AppendedQuery

I apologise for my earlier (simplistic) reply; I hadn't noticed that I had been taken to the Power BI forum as opposed to the general Excel Query forum that I normally visit.

 

[mark555]

hi @mark555,

Please share your file, so i can able help you

you Can achieve result using Power Query and Pivot Table Togetter

Here is my file, thank you so much!

 

[Peter Bartholomew]

This brought each table in from the source workbook.

let
    Source = Excel.Workbook(File.Contents("<path>_workingfile_Parts Per Rm Typical Floors.xlsx"), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Room Number", "Room Name", "Qty", "Installed", "Manufacturer", "Part Number", "Description", "Location", "Serial Number(s)", "MAC Address", "IP Address", "Jack ID", "Notes "}, {"Data.Room Number", "Data.Room Name", "Data.Qty", "Data.Installed", "Data.Manufacturer", "Data.Part Number", "Data.Description", "Data.Location", "Data.Serial Number(s)", "Data.MAC Address", "Data.IP Address", "Data.Jack ID", "Data.Notes "})
in
    #"Expanded Data"

Loading to the data model allowed the use of PowerPivot.