Pharma Batch Tracking Excel Sheets

I need a set of linked Excel workbooks that will run the day-to-day documentation for my medicines manufacturing unit. The sheets must capture every production batch from start to finish, automatically calculate key figures, and keep our quality data in one place so we can release finished goods without scrambling through paper logs.

Core workflow
• Production batch & process: One master log where I enter a new batch number and the sheet automatically expands a step-by-step form. Each step should record time-stamps, machine settings, material usage pulled from a drop-down, and calculate yields and variances on the fly.
• Quality checks & finished goods: As the batch progresses, operators will input in-process test results. The sheet should flag any out-of-spec result with conditional formatting and pull the final COA data into a finished-goods register.
• Approvals: Every batch record must end with signature cells for Operator, QA, and Supervisor. Once all three are completed, the status should flip to “Release OK” and lock the record against further editing.

Helpful automations
– Pre-loaded material and specification tables that feed look-ups in the process sheet.
– Protected formulas, drop-downs, and data validation so only the correct fields are editable.
– A dashboard (pivot or Power Query is fine) that shows today’s open batches, any quality holds, and rolling first-pass yield.

Acceptance criteria
1. All calculations, look-ups, and conditional rules function correctly with dummy data I will supply.
2. No external add-ins—everything must run in standard desktop Excel (Office 365).
3. Files pass a test run covering new batch creation, data entry, QA release, and report generation.

Please include a short note on the approach you’ll take (pivot tables, VBA, Power Query, etc.) and an estimated turnaround time.