We have a straightforward opinion on this: Excel is the wrong tool for what you are building, and continuing down this path will create more problems than it solves. The scenarios you describe, new hires, resignations, backfills, secondments, temporary overlaps, monthly snapshots, and full historical tracking, are exactly the kind of relational, temporal data management that spreadsheets were never designed to handle. You are already thinking in terms of fact tables and dimension logic, which is the right mental model. But implementing that in Excel with VBA forms is like using a bicycle to tow a trailer: technically possible, but painful, fragile, and far from the best option.
Your instinct to separate front-end and back-end data files is a sign you already sense the limitations. Excel tables can mimic relational structures, but they lack referential integrity, concurrent user support, and reliable version history. When multiple users need to edit assignments, track changes over time, and query past snapshots, a shared Excel file on SharePoint or OneDrive becomes a source of sync conflicts and data corruption. VBA forms add a layer of control, but they also add complexity and maintenance burden. Every new scenario you need to handle, like a secondment that requires temporary reassignment and automatic return, will require more VBA code, more testing, and more workarounds.
What you need is a lightweight database with a simple front-end. Tools like Airtable, Notion, or a small SQLite database with a web interface can handle your core structure, historical tracking, and work allocation layers without the overhead of Excel. They offer built-in relational tables, snapshot views, and multi-user access. Your time is better spent modeling the data architecture and defining the business logic than wrestling with VBA and file-locking issues. The clean, scalable structure you want already exists in these platforms.
A practical starting point: define your core tables, People, Positions, Assignments (linking person to position with start and end dates), and Tasks (linking assignments to work items with capacity percentages). Use date-effective records for every change, so you can query the state at any month without snapshot tables. This is a proven pattern, and any tool that supports relational data and time-based queries will handle it cleanly. You asked for input on architecture, not quick hacks. Our advice is to stop designing around Excel and start with a tool that matches the problem.