Conditional formatting is not an alerting system

A supplier documentation spreadsheet is usually one tab with six columns: supplier, certificate type, issue date, expiry date, where the PDF sits, and a formula comparing the expiry date to today. Conditional formatting turns the row amber somewhere around 60 days and red once the date has passed. That is a sound record, and plenty of plants that pass audits are running on exactly that. The data model is rarely the thing that fails.

What fails is that the colour is being asked to do the job of an alert, and a colour cannot reach anyone. Three failure modes follow from that, and they are worth naming separately because each needs a different fix. The spreadsheet only warns when someone opens it. Two people working the same file overwrite each other. And nothing in the file chases the supplier, so every red row still waits on a person remembering to write the email and remembering to follow it up ten days later.

What the spreadsheet gets right

Start here, because the replacement has to carry the same fields. The columns a QA manager settles on after two audits are the correct columns. You need the supplier, the certificate types you actually require from that supplier, the date on the document, the date it runs out, and a path to the file itself. You need to be able to hand an auditor a view of that, supplier by supplier. A spreadsheet does all of this for nothing and nobody has to be trained on it.

So this is not an argument that the record is wrong. It is an argument that the record has no alerting layer, and that the three gaps below sit in the alerting layer.

Failure one: the colour only changes when someone opens the file

A date formula compares the expiry date to today, which means it depends on TODAY, which Microsoft's own developer documentation lists as a volatile function: "Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates." The operative words are "every time that it recalculates". The triggers listed on that page are all actions somebody takes: entering data, opening the file, pressing F9, inserting a row, saving. The passing of a day is not on the list.

So the conditional format is accurate the moment you look at it, and silent on every day you do not. A certificate that lapsed on 4 August is red in the sense that the colour would appear if anyone had the file open. Nobody did. The file was correct and useless at the same time.

This matters most in exactly the windows you would choose for it to matter least: the shutdown week, the fortnight after a successful audit when everyone exhales, the two weeks a technical manager is on leave. The spreadsheet was supposed to remove a recurring diary task, and instead it created one, because the only way to be warned is to go and check.

Failure two: two people, and last save wins

The second failure mode arrives the day the file becomes useful enough for a colleague to want it. Your technical manager updates the Bracken Dairy CoA expiry from a PDF that arrived in her inbox. You took a copy off the shared drive at nine that morning, added two new suppliers through the afternoon, and saved at 16:40. One of those two sets of changes is now gone, and no line anywhere says which.

Microsoft is blunt about this for cloud files: "In general, the last change that is saved, either with the Save button or automatically with AutoSave, is the one that 'wins.'" If the file travels by email or lives on a local drive instead, it is worse, because you end up with two files and a naming convention standing in for a version history. The audit cost is specific. The auditor asks for the current allergen declaration for one supplier, you have two different expiry dates recorded for it, and you cannot show who entered either or when.

Co-authoring reduces the overwriting. It still does not give you a per-row history of who changed an expiry date, from what to what, and on the strength of which document.

Failure three: nothing chases the supplier

The spreadsheet records the gap. It cannot close it. A red row still requires someone to find the contact, write the email, wait, notice no reply, write again, and keep a note of having done so. The first email is not usually the part that breaks down. The follow-up is.

There is a quieter version of this too. The contact you have been writing to left the supplier in March, so your email went nowhere, and your sent folder tells you that you did your job. The row stays red and you have stopped looking at it because you believe it is in hand. A spreadsheet cannot tell you a message bounced.

What closing the three gaps actually requires

Three mechanics, named plainly, because each failure mode needs its own.

1. A computation that runs on a schedule, not on a page view. Certly is built to track expiry dates and recompute status on a scheduled job, with alert thresholds at 90, 60 and 30 days before expiry, and a settings screen where you set those thresholds per certificate type or as a plant-wide default. The point is that the status changes whether or not anyone opened anything.
2. One record, scoped to the plant. Suppliers, their contact details and the certificate types you require from each of them (allergen declaration, CoA, audit certificate) live in one database, with plant-scoped isolation on every query, rather than in whichever copy of the file you opened this morning.
3. An outbound path, with evidence. When it launches, Certly is built to send automated chase sequences to the supplier contact for certificates approaching expiry or already lapsed, each with a one-click upload link, logged per send. The supplier uploads through a single-use, expiring link with no login to create. Each chase in the log shows whether it was sent, failed or opened, with a resend action next to the failures, and there is a chase now button on the row for the week you cannot wait for the threshold.

The output of all three is the thing you actually hand over: a single-screen view of current, expiring soon, expired and missing per supplier, exportable as a PDF for auditor review. That export carries a two-sentence line stating what the tool does, which is that it tracks certificates and reminds people about them. It does not verify that a certificate is authentic and it does not guarantee that your plant is compliant. Those remain your judgement, made faster because the dates in front of you are current.

If you are keeping the spreadsheet this quarter

Reasonable decision, and three changes take most of the risk out of it.

• Stop treating colour as notification. Put the 90, 60 and 30 day points into dated calendar reminders for named people, so the warning arrives rather than waiting to be found.
• Keep one copy in a cloud location with version history, and give one person the right to edit expiry dates. Everyone else requests a change.
• Add two columns next to the expiry date: date requested, and date last chased. That is the pair an auditor's follow-up question lands on, and it is the pair a spreadsheet never fills in by itself.