Excel Roster Automation & Conflict Detection

I run a 300-member workforce spread across several projects and shifts (AS, BS, CS, DS, NS, plus WO, KS, FS). I need a single Excel workbook that lets me build the roster quickly and, more importantly, prevents us from assigning the same reliever to two projects on the same day.

Core functions I must see
• Automatic shift assignment via simple number keys: 1-AS, 2-BS, 3-CS, 4-DS, 5-NS, 6-WO, 7-KS, 8-FS.
• Immediate warning that stops the entry whenever a reliever is double-booked on a date.
• Support for custom shift codes so the list above can grow without breaking formulas.

The sheet should comfortably hold at least a month of data for all 300 people, use clear colour cues or pop-ups for any conflict, and remain easy to maintain by staff who know basic Excel. Pivot tables, conditional formatting, data validation or VBA are all fine as long as the final file runs without extra installs or add-ins.

Deliverables
– A ready-to-use .xlsx (or .xlsm) roster with the features above built-in.
– A short guide explaining how to add staff, extend dates and modify shift codes.
– Conflict-detection logic clearly commented so we can tweak it later if needed.

I would like the first working draft as soon as possible so we can test it with real data and sign off quickly.