Plenty of small manufacturers build their first batch number generator in a spreadsheet formula that increments a counter. That is a reasonable place to start. It breaks in predictable ways, and knowing which ones lets you decide whether it is still the right tool.
Four rules to settle first
Uniqueness: no two batches may ever share a number, including across products and across years. A counter that resets annually needs the year in the code, or last January's batch collides with this one.
No reuse: a number belongs to a batch permanently, even after that batch is consumed, scrapped or recalled. Reusing a retired number makes historical records ambiguous at exactly the moment somebody is relying on them.
Readable under pressure: somebody reads this off a label in a cold room. Avoid characters that look alike in your label font, and keep it short enough to be checked at a glance.
Recorded, not just displayed: the number is worth little unless the moment it was assigned, and to what, was written down. A generated number with no attached record is a label, not traceability.
Where the spreadsheet stops working
Two people opening the file at once is the first failure: both read the same last value and both take the next number. Nothing warns you, and the collision only surfaces when someone searches for a batch and finds two.
The second is that a spreadsheet records the number but not the events afterwards. When the question is where that batch went and how much is left, a generator alone cannot answer it, and the answer is what an auditor or a customer is actually asking for.
A workable middle
Keep the spreadsheet if it is genuinely working, but move the assignment to one place that cannot hand out the same number twice, and record each batch against its item, quantity and location at the moment it is created. That is a smaller change than replacing how you print labels, and it removes the failure that is hardest to detect.
What this site does
Control My Lots records a batch against its item, quantity, location and status, and keeps the full history of receipts, movements, consumption and corrections. It does not currently generate the number for you, so today it sits downstream of whatever assigns it.
