Designing a Resource Management data model in Excel (with historical tracking + work allocation) – how would you architect it?

Hi all,

I’m planning to build a Resource Management solution in Excel (with VBA forms for controlled data entry), and I’d appreciate input on architecture and data modeling before I start building. What I need to manage:

1. Core structure People Positions The relationship between them (who sits in which position)

2. Scenarios that must be handled New hire Resignation New position creation Backfill positions Temporary overlap (handover – 2 people in 1 position) Secondment (temporary move to another position, then return)

3. Historical tracking I need to: See the structure at any given month (monthly snapshot) Track who was in which position at what time Keep full history (no overwriting)

4. Work Allocation layer On top of the org structure: Assign work/tasks to people Tasks may require 100% or partial capacity Assignments change over time Also needs historical visibility Technical direction (initial thought) Excel-based VBA userforms for data entry Structured data tables Snapshot logic (monthly)

Questions: 1. Would you model this as: A relational-style structure in Excel tables (fact + dimension logic)? Or is Excel already the wrong tool for this level of complexity?

1. How would you design: Position ↔ Person history? Snapshot logic (event-based vs. periodic snapshot table)?

2. For multiple users: SharePoint + OneDrive? Split front-end (VBA UI) and back-end (data file)?

I’m less interested in quick hacks and more in a clean, scalable structure that can handle structural changes over time.

Thanks in advance!