Rebuilding the Instrument & Locker Tracking System for the University of St. Thomas
School music department · ~450 lockers, 150+ rental instruments, no budget, no macros, no third-party software allowed.
- First-hand experience uncovered a flawed system for checking out and tracking UST rental instruments. Check-out and tracking was split across three spreadsheets and paper forms, and inventory was getting lost.
- Designed a single append-only log in Excel. Location, occupancy, and overdue status all derive automatically.
- Rejected macros and Microsoft Forms. Non-technical staff have to be able to maintain it.
- Scaled up to ~450 lockers, ~170 instruments, 3 rooms, two buildings.
How It Works
Single Point of Entry Log
All data is entered through a log to keep a chronological paper trail of UST rental instruments.
Rental Instrument Inventory Automatically Updates
The rental catalog automatically populates with updated storage location and renter information (if applicable).
Building-Specific Locker Room Sheet Updates
As items are added to the log, the corresponding sheet for the building and storage room updates with the relevant information. A locker holding more than one instrument is treated as an array.
The Log in Practice
The same Log sheet after months of real use, entries for two full locker rooms, showing how it holds up at scale rather than just as a clean demo.
The Problem
Rental instruments and locker assignments were tracked across three disconnected spreadsheets plus paper checkout forms. An instrument's location lived in a field a human had to remember to update, and often didn't. When that happened, we lost track of where instruments were and who had them. Nobody assigned this to me; I noticed it as the department's student librarian/roadie and brought the problem to leadership myself.
What I Did
Instead of storing location as a field someone maintains by hand, I redesigned the system so location is calculated. Every checkout, check-in, or locker assignment is logged once as a single row in an append-only Log. The instrument and locker views then derive current status, holder, and location automatically from the most recent entry. Nobody edits a location cell directly.
I set hard constraints going in, because the system had to outlive me and run in the hands of non-technical staff: Excel only, no macros (formulas and data validation only, so nothing fragile to repair after I'm gone), and single point of data entry.
Built iteratively against real department data: consolidated the fragmented source files (reconciling 109 overlapping instrument records into one master list), built the Log engine, added overdue tracking and a live dashboard, then handled real-world edge cases as they came up: multiple instruments per locker, per-item release, personal vs. rental instruments, a year-end reset that preserves history, and eventually extended the whole system to three locker rooms across two buildings.
Outcome
One self-maintaining Excel workbook covering ~450 lockers and 150+ instruments. Data is entered once; locations, occupancy, overdue flags, and the dashboard all update by formula. No macros, no third-party tools. Built to be handed off and keep running without me.
← Back to portfolio