How Do I Combine Data From Multiple Worksheets Into One Summary?

Hello All!

I couldn't find an exact solution to my issue already, so I'm hoping someone can help!

I have an Excel workbook, which contains multiple sheets. We'll say Location1, Location2, Location3, Location4, and Location5. On each worksheet, which have all been formatted exactly the same, there are multiple columns all with the same names: Cost Center, Personnel ID, Last Name, First Name, Full Name (Concatenated From Last Name & First Name), and Bonus Award, etc. My goal is to create a summary sheet, or pivot table, that shows all of the information from the Cost Center, Personnel ID, Full Name, and Bonus Award in a summary from all of the sheets, without having to copy/paste each sheet into a "Master," sorted by Personnel ID, regardless of branch location. Whatever the solution is, it will need to be dynamic, because these sheets are updated monthly, based on the information in the columns to the right, as each employee submits a Safety Form throughout the year-long period covered. It will also need to update based on the addition of new employees, and deletion of those that leave. Each of my sheets contains a table, labeled as Table1, Table2, and so forth, of the first 6 columns, by how many ever rows there are, on each sheet. They vary because the number of employees varies by location.

I tried to create a pivot table, using Power Pivot, but when I tried to define the relationships between the different tables, I got an error. I can't remember the exact verbiage, but it was something to the effect that the relationships couldn't be defined because of a many-to-many data issue. The other issue I have is that this file is shared by many users, so it is saved on MS Teams. Thus, auto-save has to remain functional.

Maybe a Power BI Report would be better suited to create the results I'm after, but I've never worked with it, so I have no idea if it would be a better solution.

My sample only contains one worksheet. I couldn't share the full working file, although it is relatively small, as it contains confidential information.

Look at this video on combining (appending) multiple worksheets. You can then pivot the data once it is combined.

https://www.youtube.com/watch?app=desktop&v=HcfKOusOJhA

Thanks for the Rep. Glad to hear you have it working.