Excel Commission Spreadsheet Integration

I maintain four separate Excel workbooks:

• a master list where every new commission is recorded,
• an individual statement template that shows each adviser a running total of their income,
• a month-end summary that splits total business into areas such as Life and Buy-to-Let mortgages, and
• a compliance tracker where mandatory documents, high-risk flags and full-check scores are logged.

Right now I copy data between files by hand. I need these workbooks to talk to one another so that:

1. New rows added to the master commission sheet flow automatically into each adviser’s statement.
2. The month-end summary refreshes with an accurate breakdown by business type without extra keystrokes.
3. Compliance fields entered on the commission sheet (mandatory docs, risk level, audit scores) populate the central compliance workbook in real time.

I am happy for you to use whatever works best inside Excel—Power Query, VBA macros, dynamic arrays, pivot tables—so long as the solution stays within the Microsoft 365 environment and can be maintained by a non-coder once it is built.

Deliverables
• Connected workbooks with automated data flow and category calculations.
• Clear documentation (or brief loom/video) showing how to refresh, add new advisers, and adjust categories.
• A short hand-over session to test everything with live data and confirm the results reconcile with our current manual process.

Acceptance criteria
• Adding a commission line in the master file instantly appears on the correct adviser’s statement.
• The month-end sheet recalculates totals and percentages for each business line without manual formulas.
• Compliance data carries across with 100 % accuracy.

If you have questions about structure or naming conventions, let’s address them early so we can keep the build clean and robust.