Color-Coded Roster Excel Sheet

I want a single, easy-to-use Excel workbook that lets my team build our roster simply by typing the numbers 1-7. As soon as a number is entered, it must automatically translate to its shift code (1 = AS, 2 = BS, 3 = CS, 4 = DS, 5 = NS, 6 = WO, 7 = FS).

To make the schedule clear at a glance, each shift should be highlighted in its own color through conditional formatting. I also need a built-in weekly summary report that totals how many staff are on each shift and how many are off (WO or FS) for the week. A separate tab for the report is fine, or a dynamic table that refreshes automatically when the roster is updated—whichever you think works best.

Please keep everything contained in the workbook with no external links. If a small VBA snippet or custom function makes the experience smoother, feel free to include it; otherwise, formulas alone are perfect.

Deliverables
• Excel file with number-to-shift conversion fully functional
• Color-coded visual indicators for all seven codes
• Auto-updating weekly summary report

I’ll test it by entering a sample week’s data; if the colors switch instantly and the weekly report reflects accurate counts, we’re good to go.