West Forecast Tracking — field logic

Upload all four every week. The tracker is the file being written; the other three are the sources it reads. Names carry a date or version number that changes weekly — match the current cycle.

• Forecast Tracking [date] EOD (West).xlsx

  Role

  The tracker being updated — the deliverable.

  Reads

  Prior-week notes G–J, updated material notes K–O, and Manf Lot.

  Writes

  Current-week C estimate, D category, E ship, F comment.

• Daily Filling Schedule [month].xlsx

  Role

  Packaging (fill) dates — sets the Fill Finish date and the estimate month.

  Reads

  Summary - Torrance

  Gives

  Latest scheduled fill date per lot / order.

• Compounding Schedule PML West 2026.xlsx

  Role

  Batching status & dates, and the comments scanned for red flags.

  Reads

  current + prior month tabs

  Gives

  Batch status, batch/CMP date, and blocker comments per lot.

• West Upcoming Releases (#).xlsx

  Role

  Release / material corroboration — confirms “Released” and enriches material notes.

  Reads

  summary

  Gives

  Release month, Initial / Ivette / General comments, Rolling Check.

Every source is matched to a tracker row on Manf Lot first, then order number as a fallback.

• Primary key — Manf Lot. The lot on the filling and compounding schedules is matched to the tracker’s Manf Lot. When a specific order number isn’t listed on the fill schedule but its lot is, the row is still counted as scheduled (lot-level match).
• Resolve columns by header name, not position. Manf Lot and the material columns shift position between weekly file versions. Columns are always found by their header text — never a fixed letter.
• Normalize lot codes. Excel silently turns codes like 6E006 into scientific notation; both sides of a join are normalized so those still match.

C–F are rebuilt fresh each week; E carries the prior week’s commercial call.

Each written row is resolved by the first rule it matches, top to bottom. A row is written when it has prior notes, is RLS/STD, appears on the fill schedule, or has an active batch status; dormant NST rows with none of these stay blank.

1. DONE stays DONE
   A row previously marked DONE keeps it — even if its completed fill still shows on the historical schedule.
2. New, not yet scheduled → “New”
   Left as New for review. Once a new order reaches the fill schedule it drops to rule 3.
3. On the fill schedule → schedule comment
   Order listed → Fill Finish template; only the lot listed → lot-level template. Estimate = fill month, category = Released.
4. Line Trial → held as a trial
   Estimate and category read Line Trial; the schedule line is shown if the trial is scheduled.
5. Released / batching, not scheduled
   Not on Fill Schedule – Batch Status, category Released.
6. Material-constrained → refreshed note
   Comment is rebuilt from this week’s material note (Initial Comments); estimate, category and ship carry forward.
7. Otherwise → carry prior week forward
   Nothing new in the sources, so the prior comment stands.

Ship (E) carries forward in every case. Released status is confirmed against West Upcoming Releases.

Same wording as prior weeks. The delta note compares this week’s Fill Finish to last week’s.

The compounding status code is translated to plain wording. When a lot appears in more than one week, the most advanced status wins.

The current compounding comment for each lot is scanned for blockers. A blocker is appended to the F comment as a red run; the rest of the comment stays in normal text.

• Carried flags get re-colored. When last week’s red flag rolled into this week’s prior comment as plain text and the blocker still applies, it’s turned red again rather than duplicated.
• Scope is the compounding schedule. Holds that come only from the material tracker (customer holds, reformulation notes) or a raw-material quarantine list on a material row are kept in normal text, not red.
• Line trials and “New” rows aren’t flagged — they’re handled separately and reviewed directly.

Things that change week to week or trip up a naive read.

• Column positions move. Manf Lot and the material columns land in different letters across file versions — always resolved by header name.
• Don’t force a recalc. These workbooks use XLOOKUP, which the offline recalc engine can’t evaluate and would blank out. The file is saved as-is; Excel recomputes the Totals and pivots on open.
• Scientific-notation lots. Alphanumeric lot codes matching a digit-letter-digit pattern get converted by Excel; both sides of every join are normalized.
• Historical schedule dates. A completed fill still shows on the schedule with a past date — that’s why DONE takes priority over a schedule match.
• Ship is judgment. Column E is never inferred from the schedules; it carries forward for you to adjust.