Case Study • Process Automation • Project Tracking & Reporting
Scattered spreadsheets. Endless emails. Gone. One database, one report.
A centralized project database and one-click board reporting system, built in 2023 for a commercial general contractor running multiple public agency projects.
The Challenge: How long does it take to build a clean status report from three months of email threads? An hour? Two hours? A whole afternoon?
Whatever the guess, the real answer is “too long.” Every time.
The contractor ran several public agency projects at once, each with its own design team, owner reps, consultants, and a steady stream of meetings, action items, and scope changes. All of it lived across inboxes and standalone documents — there was no consistent, protected system a project engineer could just pick up and run.
The bottom line: The system meant to keep everyone on the same page was the reason no one ever was.
Structured
One protected workbook per project. Databases for milestones, meetings, action items, and scope/VE changes — each with its own input sheet.
Automated
VBA macros ran the directory import, task completion, and one-click reporting. Event macros guided every entry.
Tailored
Built around real construction workflows — DBE compliance tracking, meeting logs, CSI-coded scope and value-engineering trackers.
How I Built It
What data is needed?
Primary Data
Contacts, Meetings & Trackers
Project contacts by role, meeting logs, owner action items, and CSI-coded scope/VE tracker items.
Metadata
Compliance & Categories
DBE flags, meeting-type and CSI code lists, and discipline categories feeding every dropdown.
Where does it come from?
Primary Data
Internal Databases
Every dataset — milestones, meetings, action items, issues, scope changes, and project status — had its own Excel database and supporting input page.
Primary Data
Imported, but Editable, Directory
Project contact directory imported from external systems, but directly editable within the workbook. Y/N toggles indicated who should be included on which reports.
User Input
Guardrails, Not Guesswork
Event macros guided every entry on every input sheet, leading the user intuitively through the process. Admin and status sheets hidden from users entirely.
What do you need to do with it?
Simplified Reports
One-Click Board Report
Contacts, calendar, meetings, and action items in one report — filterable by owner via slicer. The report that used to take an afternoon now takes a single click.
Customized Reports
Owner and DBE Reports
Select the report type, the date span, the pages to display, and the owners to include. Generate and save the report instantly.
Dashboards
Filterable Trackers
Trackers filterable by type, due-date window, and completion status. See what you want, and only what you want.
What Was Built & Verified
Development reached roughly 85% completion when the engagement concluded — no adoption data available, but this was built and confirmed working in testing.
- A one-click, one-page report pulling contacts, calendar, and DBE action items live from the database
- Detailed, fully customizable reports generating the exact information needed for each owner to keep the project on track
- Owner action items with automatic red-flagging of overdue dates, providing a built-in early-warning system
- A contact-directory report correctly rolling up every firm into one list
- Scope/VE trackers functioning with CSI-code tagging, filterable by acceptance status
A Note on How This Has Evolved
This build is about three years old, and the architecture shows it — the database updates and one-click reporting were VBA-driven. The same core structure holds today, but most of that work now happens with self-referencing queries and Lambda functions instead of VBA. This results in less code to maintain and faster build times, all without changing the user experience.
A Note on Confidentiality
This project is not covered by an NDA. In the absence of a documented close-out with the client, this entry describes them by role rather than name. All names, addresses, and identifying numbers in the source file are placeholders.