Backfill Lot & Expiry Data
Budget: $30 – $250 CAD
My inventory is tracked in a single Excel workbook that already uses formulas to monitor quantities, but every historic line is missing its lot number and expiry date. To stay compliant with GMP traceability requirements I now need those two fields populated and protected against future errors.
The job breaks down into two clear parts.
• First, take the existing spreadsheet structure and design a semi-automated way—think helper formulas, data-validation, and, if you see fit, a light macro—to backfill all past rows with the correct lot number and corresponding expiry date. Historic data will come from packing slips I’ll supply as PDFs.
• Second, extend the same mechanism so that any new receipt entry prompts me for a lot number and validates the calculated expiry date automatically, preventing gaps from re-appearing.
Acceptance criteria
• Every current stock line displays a verified lot number and expiry date that matches the source document I provide.
• The workbook remains formula-driven so I can understand and audit it; any VBA must be clearly commented and optional to enable.
• A short user guide (one page is fine) explains how to keep using the sheet under this semi-automated flow.
The file and sample source documents are ready to share as soon as we start, and I’m happy to answer domain-specific questions about GMP rules while you build.
The job breaks down into two clear parts.
• First, take the existing spreadsheet structure and design a semi-automated way—think helper formulas, data-validation, and, if you see fit, a light macro—to backfill all past rows with the correct lot number and corresponding expiry date. Historic data will come from packing slips I’ll supply as PDFs.
• Second, extend the same mechanism so that any new receipt entry prompts me for a lot number and validates the calculated expiry date automatically, preventing gaps from re-appearing.
Acceptance criteria
• Every current stock line displays a verified lot number and expiry date that matches the source document I provide.
• The workbook remains formula-driven so I can understand and audit it; any VBA must be clearly commented and optional to enable.
• A short user guide (one page is fine) explains how to keep using the sheet under this semi-automated flow.
The file and sample source documents are ready to share as soon as we start, and I’m happy to answer domain-specific questions about GMP rules while you build.