Excel Help Needed – Linking sheets without dynamic sort and filter

Hello Excel Community!

I’m reaching out for some advice on automating data flow between two Excel spreadsheets, and I’m facing a challenge that I can’t seem to overcome.

Here’s the situation:

I have a Master Employee List containing all employees’ details (name, employee ID, start date, etc.). This list is frequently updated with new sales employees who need to be tracked separately for finance purposes.

I want to create a Finance Spreadsheet that automatically pulls sales employees’ names, employee IDs, and start dates from the Master Employee List. This way, I don’t have to manually enter new sales employees every time the Master list is updated.

The challenge:

My manager prefers all data to be in table format, but I’ve encountered an issue when linking the Master Employee List to the Finance spreadsheet. In my Finance spreadsheet, I have both dynamic columns (linked to the Master Employee List) and static columns (for manually entered data like commissions, which I update monthly).

The issue arises when I sort or filter the Master Employee List: when a sales employee (e.g., John Doe) is filtered out or moved around in the original Master list, his name disappears from the Finance spreadsheet, but the manually entered commission data remains. This causes the commission data to be misaligned, as it is then allocated to the wrong employee in the Finance sheet.

What I need:

• A solution that allows me to automatically pull only sales employees into the Finance spreadsheet.
• A way to keep the manual commission data intact even if an employee is filtered out or re-ordered in the Master Employee List, without causing data misalignment.
• Since table format is required by my manager, I need a solution that works within table constraints. Please note that not all formulas work seamlessly inside tables

Questions:

1. How can I set up the Finance spreadsheet to pull in sales employees dynamically without losing the manual commission data when employees are filtered or moved?
2. What’s the best approach to work with tables in this scenario, especially considering the limitations of certain formulas?

Any advice or tips would be greatly appreciated! Thanks in advance for your help!