•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Designing a Resource Management data model in Excel (with historical tracking + work allocation) – how would you architect it?
Our take
Designing a Resource Management data model in Excel can transform how you track personnel and allocate work. By architecting a solution that captures historical data, manages position changes, and allows for flexible task assignments, you can create a powerful tool for your organization. Consider using structured tables and VBA for controlled data entry to ensure scalability.
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:
Core structure People Positions The relationship between them (who sits in which position)
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)
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)
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?
How would you design: Position ↔ Person history? Snapshot logic (event-based vs. periodic snapshot table)?
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!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Architecture for two separate projects: A Nationwide Template and an Automated 30+ Page ReportI am designing a data solution that needs to scale from a local proof-of-concept to a nationwide implementation. I need advice on the most stable "tool stack" to ensure reliability for non-technical end-users. Technical Proficiency / Skill Set: Power Query: Self-taught, comfortable with ETL and merging multiple data sources. VBA: Basic/Intermediate (primarily used for automation, often AI-assisted). Web/Coding: Very basic HTML/CSS knowledge, but no JavaScript/Python experience. Context: I have a logical workflow mindset, but I am seeking more of the "industry standard" to move away from manual, repetitive processes. Project A: The Nationwide Data Template (Standalone Tool) The Goal: Build a data setup to be implemented nationwide for other court locations. Input: Must process CSV files from a central authority (no direct database access due to high security). Users: Staff at all technical levels. They should only interact with 1-3 specific "result" sheets. The plan is that they will be told only to use one specific folder and of course the sheets will be protected. Constraints: I will have no access to their local folders or machines for troubleshooting. The tool must be "bulletproof" and handle data refreshes without user-side errors. Question: Is Power Query + VBA robust enough for a rollout with zero local support, or should I move the logic to Power Pivot/DAX to reduce the risk of users "breaking" the tool? Project B: The 30+ Page Reporting Nightmare (Internal Workflow) The Goal: Automate a 32-page yearly report that I personally produce. The Problem: Currently, I manually copy/paste tables and charts from Excel into Word. I started with keeping the chains but the document became so heavy it was near-crashing, and because it's so unstable, I've had to break the chains and make the data static. The Requirement: I need a smarter "bridge" between Excel and a formatted report (Word or PDF) to replace the manual "Ctrl+C / Ctrl+V" marathon. Question: What is the industry standard for this? Is this where I move to Power BI, or is there a reliable way to automate Word via Excel objects that won't crash the document? Technical Questions: Stability: For a rollout with zero local support, is a Power Query + VBA combination robust enough? Would moving the logic to Power Pivot/DAX improve stability and reduce "breakability" for end-users? Reporting Pipeline: What is the industry standard for pushing large amounts of dynamic Excel data into a formatted 30-page report? Is Power BI the logical successor here, or is there a reliable way to automate Word via Excel (e.g., Content Controls or specific VBA objects) that won't crash? Future-proofing: Given the need for a national standard, should I prioritize mastering Power BI or deepening Advanced VBA/Data Modeling within Excel to solve these specific deployment issues? Excel Version: Microsoft 365 (Build 17928.20440), Semi-Annual Enterprise Channel. submitted by /u/screaming-Zebra [link] [comments]
- Creating an Auto Scheduler for my Work’s DepartmentI realize I may go down a rabbit hole and this could potentially take me months or maybe even longer, but that’s fine I just need starting points for my research. My experience: Not a lot. Limited to extremely basic functions (e.g. SUM, IF, AND, OR), conditional formatting, data validation. Willing to delve into VBN but I know that requires basic knowledge of programming. Goal: There’s ~40 people in my department and we have an excel sheet that tracks all the daily tasks that need to be completed. There’s myself and a manager that makes the schedule for our respective sides of the week and it takes us 2-3 hours each week to assign each task one by one until the schedule is filled out. I would like to automate this in some kind of way. Ideas: - Assign a theoretical/average time to complete a task. - Set each task as either an AM or PM shift specific task, and other tasks that can be completed by either shift. - Assign people to a specific shift, and how many hours their shift is and on what days. - If people have planned PTO, being able to update the available analysts that Excel will take into account. - Excel does the work of auto-populating tasks with a person for each task accounting everything above and not assigning tasks that would cumulatively exceed the number of hours they’re at work, as well as averaging the workload between each person. - Dynamic and editable after cells are auto-populated. Truthfully this is not required, but a quality of life. People call out, emergency situations might come up that might require attention over daily tasks, etc. My thought is I can copy the results and paste it into a separate sheet that can be editable as the week goes on. Again I’m sure I have no idea how much work I’m asking for and it may be way above my capabilities, but as you roast me in the comments at least leave something that I can use to help me get started. Appreciate yall. submitted by /u/GoldJomell [link] [comments]
- Is there any way to make a relational DB like thing in excel?For context, my work involves maintaining a lot of excel trackers. Basically we maintain these to track project details for a client like project deliverables, project codes (for employee clock-ins), project milestones, project assigned to PMs or not, etc - all in different excel files. This might sound like simple info, but we capture a lot of details related to project in all those files - like the main tracker will have bascially columns for capturing info from every section of the contract signed with client. The clock-in codes tracker will have its name, parent account ID, clock-in category, project ID, and a few other columns. Just adding one project's details to all trackers takes about 30-50 mins right now (depending on complexity and category of the project). However, maintaining multiple files leads to a lot of duplication effort - basically you add name of the project, project ID, PM name etc so many times. Anyway this can be changed? Like we add all data in one sheet and maybe pull it into different views for different purposes? I have done some research with gpt and on youtube, but they suggest going the power apps/ power BI way, but I am not too well-versed with those. And I was thinking if there is another solution that can be done in excel itself? Or if power BI is the way, then maybe can you guide me to a starting point for that? Thanks in advance. submitted by /u/WorldlyDot_1 [link] [comments]
- Optimizing data entry in Excel file on SharePoint w/ 5 users: Is MS Forms + Power Automate the way?I want to optimize data entry for an Excel spreadsheet stored on SharePoint, used by 5 data enterers. It has around 300 entries/rows and 17 columns on the main worksheet. 4 other worksheets contain additional variables/columns for the same entries on the main worksheet. 8 other worksheets contain extensive instructions for the systematic literature review we are conducting. Edit: Our current approach has each of us open the Excel file in our desk top app for data entry. The issues I want to solve are: Improved data entry environment: 1) Some cells end up with extensive/long text entries. Users resize cells to optimize their view, but that screws up other people's views. We need data entry solution that allows large data entry areas for certain Excel cells, but not others. 2) Decrease users mistakenly entering data in wrong cell. formatting issues caused by each user doing their own rogue formatting (Despite discussing not messing with formatting in couple of meetings, these people, led by our boss, keep doing it!) excel sheet freezing for short periods of time mouse pointer highlighting cells above the cell to which it is pointing A bit of Googling led me to MS Forms + Power Automate. Would this solve the issues described above and be stable. Is there a better solution? Background Info I'm an intermediate/advanced excel user with experience in VBA, SQL, and stats packages (eg, SAS) working at a large federal agency Excel version: MS 365 MSO v2603 We have MS Forms and Power Automate in-house submitted by /u/tenbsmith [link] [comments]