The situation
Every resurfacing machine logged its production on an activity sheet. The sheets worked, in the sense that the data existed. But looking up what happened across a season meant opening production reports one at a time, and the data inside them varied enough between operators that comparing it was unreliable.
Two problems, and the second could not be fixed without the first: you cannot consolidate data that was not captured consistently.
What we built
A replacement template, then the consolidation layer it made possible.
- Data validation lists, conditional formatting and inline error checking, so the sheet resists being filled in wrongly rather than merely recording that it was.
- A structure designed from the outset to be machine-readable, not just human-readable.
- A Power Query model that loads every activity sheet into a single consolidated production history.
The result
Historic production is now a query rather than an excavation. Because the consolidated data is clean, the team can report on it directly, including the measures that were previously too laborious to track: average production rates, and lost time attributable to breakdowns.