The Dispatch Spreadsheet That Ran a 40-Truck Fleet

Right reference, wrong address

The driver has the right reference number and the wrong address. He calls from a tile wholesaler's yard in Baunatal with eighteen pallets of potting compost on board, and the man in the yard has never heard of the order.

Timo, the younger of the two dispatchers, finds the row in the Orders tab. The reference is right, and so is the customer's name. The street and postcode belong to a different order. On Wednesday someone sorted the tab by postcode with only that column selected, and since then twenty-three orders have carried someone else's delivery address. Excel did exactly what it was asked.

The company is invented: a family haulier outside Kassel with 40 trucks on a normal day, 26 of them its own and the rest subcontracted, all planned in one workbook on SharePoint called Dispo_2026_Petra_NEU.xlsm. Petra, the lead dispatcher, built it over seven years. She repairs the twenty-three rows before lunch, from the order emails and her memory. Nobody else could have.

Forty tabs and one author

The workbook is good, which is the problem. Orders, Fleet, a week plan, customers, rates, a tab per subcontractor and a copy of everything per month going back to 2020. Red cells in the Fleet tab mean workshop. A macro builds the drivers' run sheets, except for anyone who opens the file in a browser, because macros do not run in Excel for the web. Driver hours live in the payroll clerk's own file.

It won because the person who knows the work built it and can change it the same afternoon. When a new customer arrives with delivery rules, Petra adds a column and a color, and the rules are live. No software the company was ever sold did that.

Arne, who runs the business his father started, can see where it stops. In January a garden-center chain's regional contract begins, with ten more trucks and two more dispatchers, and a dozen people already edit the same rows. Rules live in colors and comments nobody wrote down. Excel keeps file versions, and recent versions show who edited a cell, but nothing stops an impossible entry: one truck in Kassel and Hamburg on the same morning, a delivery before its pickup, a driver planned past his hours. Nothing stops a sort.

Last Tuesday, on a shared screen

Arne calls me the week of the compost. I ask for one thing: Petra, the workbook open, and time to walk me through a real day, from the first email to the last confirmation. I want to see what she does, not what the tabs say.

It takes two hours on a shared screen. She types the weekend's orders in from the shared mailbox: PDFs, emails, one photo of a handwritten note. She reads availability from cell colors. When a customer moves a pickup she changes the date in Orders and again in the week plan, because in March someone pasted a value over the formula that used to do it. Late in the afternoon she copies confirmations into email templates, one by one. At month end, completed jobs are typed into Business Central for invoicing, and every month a few turn up that were never marked delivered.

My first answer is not a build. If the work were a shared list with a few statuses, Airtable or Power Apps would replace the workbook in days and Petra could keep changing it herself, and since the company runs on Microsoft 365, Power Apps is where I would start. But this data has real structure: vehicles, drivers, stops, time windows and rate agreements, with rules across all of them. Subcontractors should see their own jobs and nothing else. Delivered jobs must reach Business Central once and never twice. That combination is where custom software earns its cost.

I also say no to two things on that call. The tool will not rebuild the workbook tab for tab: it replicates the work, and nothing gets built just because it was in the spreadsheet. And no model assigns a truck. AI gets two narrow jobs, reading messy free text during the migration and drafting bookings from order emails, and a dispatcher makes every assignment.

Rules that were never in a formula

The most valuable thing in the first two weeks is not code. It is the list of rules Petra carries that exist in no formula. We find them the way we found the first ones, walking through real weeks on a shared screen, in sessions booked in advance because her time is the scarcest input in the project. Each rule goes on a list she signs off. A few of them:

The second row is the one that keeps what made the workbook work. Delivery rules change whenever a customer asks, so Petra edits them herself, the same afternoon, on a settings screen. What never changes, such as one vehicle in one place at a time, is enforced by the database and covered by tests. The full mapping from tabs to tables, and a traced migration run, are in the blueprint for turning a spreadsheet into an internal tool.

Tabs that hold things, tabs that show things

The data model is agreed with Petra before a single screen is drawn. Tabs that hold things, such as orders, trucks, drivers and customers, become tables in Postgres with constraints that reject impossible entries. Tabs that are really views, like the week plan, become screens built from those tables. Formulas become tested code. Colored cells become statuses with allowed transitions, and "delivered" needs a proof of delivery or a named person confirming it. People sign in with their Microsoft accounts, so roles follow the company directory: dispatchers plan, sales maintain customers, everyone else reads. Every save writes who, when, the old value and the new one to an audit table.

The migration is a script, not a weekend of copy and paste, and it runs again and again against fresh copies of the workbook. The first dry run flattens merged cells, merges one customer spelled five ways, and rejects a few hundred orders: deliveries dated before their pickups, trucks the company sold years ago. Those rows go back to Petra in an Excel file, she corrects what she knows, and the script runs again. A small model reads only the free-text notes, turning "HB, mornings only, call first" into fields, each value linked to the words it came from, and Petra checks a sample by hand.

Order emails get a draft, never a booking. A model fills the booking form from the email and marks anything it cannot find as missing. Code validates the postcodes, matches the customer to Business Central by VAT ID and tests weight and dates against the vehicle and the window. The draft opens beside the email, and nothing becomes a booking until a dispatcher saves it. Switch the model off and the same form is filled in by hand while everything else keeps working, which is the test I hold any AI feature in an internal tool to.

The export at ten to seven

One conversation comes before cutover. The staff elected a works council two years ago, and a log of who changed what also shows how each person works. Section 87(1) no. 6 of the Works Constitution Act gives a works council a say over technical systems that can monitor behavior or performance, so it hears about the log early, with a written statement of what the log is for, tracing mistakes and finding where the tool is slow, and what it is not: a way to rate anyone.

Then, on a quiet Sunday, after a last dry run comes back clean, the final import runs once. From Monday the dispatchers plan in the tool, and the workbook stays on SharePoint, read-only, for reference. It is not deleted.

On the third morning the export count shows what I was watching for: the whole week plan exported to Excel at ten to seven and again before ten, both times by Petra. Repeated full exports by one person usually mean someone is still planning in Excel.

She is. When the Kaufungen garden center moves Thursday's deliveries to Friday, which it does most weeks, the workbook let her select eight rows and drag them. The tool makes her open eight bookings. She is not being stubborn: the tool is slower than Excel at a common task, and that is exactly how a shadow spreadsheet starts. That week the planning board gets multi-select, bulk moves and keyboard shortcuts for the things dispatchers do most, and the project gets a rule: a common task that is slower than in the workbook is a bug, not a training problem. The morning exports stop.

What Petra still changes herself

The compost cannot go to Baunatal the same way again. An address belongs to a stop and a stop to a booking, so there is no column to sort out of line. A truck booked twice is refused when someone tries, not discovered on the motorway. A pickup moved after the driver has left alerts the dispatcher on shift in Teams. Delivered jobs go to Business Central with the job ID as the reference, so a retry cannot bill twice, and a delivered job without an invoice after two days shows up that week instead of at month end.

Petra's week keeps what she liked. She sets up a new customer's delivery rules the afternoon the customer asks. The week plan still looks like her week plan. Every view exports to Excel with real dates and numbers and stable column names, because her Friday pivot table on subcontractor costs is how she watches the margins, and an export that breaks it would send her back to the workbook. What she no longer does is carry every rule in her head for everyone else. When she is off, Timo plans with the same rules she would, and the two new dispatchers in January learn them from the tool.

A tool like this is custom software on a deliberately boring stack, quoted per scope after a free 30-minute call, because three screens over one table is a different job from a planning system with rules, roles and an ERP link. The data model, the state of the old data, the integrations and the permissions move the price. The internal tool blueprint has the six ways these projects fail, and if a shared list might be all you need, weigh no-code against custom first.

Frequently asked questions