Case Study · Construction — Public Agency Projects
Centralized Project Tracking & Reporting for a Construction GC
Contacts, meetings, action items, and scope changes lived across email threads and standalone documents — with no consistent, protected system a project engineer could just pick up and run.
At a Glance
Client / Sector
A commercial general contractor serving public agency clients
Engagement Type
Excel-based project database template with auto-generated one-page reporting
Duration
12 weeks of development and testing, reaching 85% completion before the engagement concluded
My Role
Sole consultant — requirements, architecture, development, testing
Tools & Techniques
Excel, Power Query and structured Tables, VBA (macros, UDFs, event-driven), conditional formatting, data validation, protected/hidden admin sheets
Key Deliverables
Contact directory importer, milestone/meeting/action-item/scope trackers, auto-generated one- or multi-page project report
Structured
One protected workbook per project; Excel databases for key datasets (milestones, meetings, action items, scope/VE changes), each with a dedicated, user-friendly input sheet.
Automated
VBA macros ran the directory import, task completion, and one-click reporting. Event macros guided the user through the spreadsheet and facilitated database updates.
Tailored
Built around construction-specific workflows: DBE compliance tracking, owner/design-team meeting logs, CSI-coded scope and value-engineering trackers.
The Challenge
The client ran several public agency construction projects at once, each with its own design team, owner representatives, consultants, and a steady cadence of meetings, action items, and scope changes. That information lived across email threads and standalone documents per project, with no consistent, protected system a project engineer could pick up and run. Assembling a clean status report before an owner meeting meant manually compiling everything from scratch.
How It Was Built
What data is needed?
Primary data
Project overview and key contacts by role; meeting log entries (date, time, duration, type, location, topic, attendees); owner action items (description, owner, priority, due date, status, notes); scope and value-engineering tracker items (CSI code, discipline, dollar/time impact, acceptance, ball-in-court).
Metadata and rules
DBE compliance flags, meeting-type and CSI code lists, company/discipline categories feeding every dropdown.
Where does it come from?
A directory-import routine pulled contact lists into a protected per-project database; project staff entered and updated meetings, action items, and tracker data on dedicated input sheets, inside structured Tables with locked formula columns and validation.
VBA event macros kept every user interaction inside the guardrails, with admin and status sheets hidden from users entirely.
What do you need to do with it?
A one-click, one- or multi-page project report combining contacts, calendar, upcoming meetings, DBE action items, owner action items, and an issue tracker, filterable to all owners or a selected subset via slicer.
A companion contact-directory report rolling up every contact across every firm on the project into one exportable list.
Trackers filterable by type, due-date window, and completion status, with report controls that auto-hid themselves when a user navigated away from the report view.
What Was Built & Verified
Development and testing reached roughly 85% completion when the engagement concluded, before full production rollout, so there’s no adoption or time-saved data available. What’s below was built and confirmed working during testing.
- The one-click, one-page project report: contacts, calendar, upcoming meetings, and DBE action items pulling live from the underlying database.
- Owner action items with automatic red-flagging on overdue due dates, a built-in early-warning system instead of a manual status review.
- The contact-directory report correctly rolled up every contact across every firm into one exportable list.
- Scope and value-engineering trackers functioning with CSI-code tagging and dollar/time-impact fields, 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 (one protected database per project, one set of guardrails protecting the user), but most of the database and reporting work now happens with self-referencing queries and Lambda functions instead of VBA, less code to maintain, faster recalculation, a build that comes together quicker, without changing anything about what the user experiences.
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; one project identifier and the meeting-attendee detail visible in the original screenshot have been redacted for consistency.