How do I create a master list excel sheet that extracts data from multiple other sheets in the same workbook?

Question

I have a workbook with one master list that captures each employee's details for employees who are employed at any given point over a 12 month period.

I then have 12 other sheets in the same workbook - one for each month - that gets updated as and when required, and within the qualifying month as the year goes on.

I want to be able to add and update info in the 12 sheets throughout the year and it should automatically capture this data in the masterlist without duplicating any data, but it should also slot the new info into the correct row on the master list (not necessarily the first empty row)

For example, a new employee is appointed in June. I want to add the info to the June sheet, and it should automatically populate in the master list. For exiting employees, their data should remain on the master list and not be removed.

Any ideas on how to do this?

Answer 1

Excel 365 Pro Plus with Power Pivot and Power Query.

Create Master Pivot Tables from 5 monthly sheet Tables.

With various employees and their 4 data categories.

With Slicers and PivotChart.

Master Power Query available.

https://www.mediafire.com/file_premium/ph1ke9zawqxjkvj/01_07_25.xlsx/file

https://www.mediafire.com/file_premium/gjpx3oyuirbw93l/01_07_25.pdf/file

Answer 2

Hi Nabeelah_173,

Thanks for visiting Microsoft Community.

It seems you are discussing a mechanism that automatically adds data updated over time in sub-tables to specific positions in the master table. When new data is added to a sub-table, it is synchronously added to the correct position in the master table, and when data is deleted from a sub-table, the corresponding data in the master table needs to be retained. This level of automation undoubtedly requires VBA scripts to achieve. The script should periodically check each sub-table and record changes.

There are many details that need to be refined. For example, in what order should the data in the master table be arranged? How should the data in the sub-tables be added (always at the last row, or inserted in the middle)?

Additionally, it is worth noting that this real-time information capture mechanism will obviously cause significant lag in the workbook. I think it would be better to update the master table content periodically by manually running a macro.

If you have any thoughts or updates, please feel free to share them with me.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 3

I want to be able to add and update info in the 12 sheets throughout the year and it should automatically capture this data in the masterlist without duplicating any data, but it should also slot the new info into the correct row on the master list (not necessarily the first empty row)

Hi,

1/ save your workbook as xlsm (macros enabled workbook)

scenario

existing data in your workbook

12 sheets (jan, feb,.... dec)

and a MASTER sheet

pic1

![Image](https://learn-attachment.microsoft.com/api/attachments/0a77c6b4-e450-4fa2-90b4-740b4a4531a1?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/679cec7f-38f6-43c3-a5ff-ded19ee1f4b5?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">![Image](https://learn-attachment.microsoft.com/api/attachments/679cec7f-38f6-43c3-a5ff-ded19ee1f4b5?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/6d7bc2ff-151a-430c-8c08-db53afd88424?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">

.

pic4

![Image](https://learn-attachment.microsoft.com/api/attachments/5454b03f-092e-4a23-92d9-55641b09a28f?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/08d63161-9dd9-4ab8-bcfa-59633d532d04?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">![Image](https://learn-attachment.microsoft.com/api/attachments/08d63161-9dd9-4ab8-bcfa-59633d532d04?platform=QnA