The timesheet nobody can type into
A timesheet template goes to 60 staff. B4:H10 hold hours and are validated as whole numbers between 0 and 12 with an input message; row 11 totals each day. The workbook is protected with a password, and staff report they cannot type anywhere - every cell refuses input. Two staff pasted 15 into a cell and validation raised no objection. When printed, the sheet runs to three pages and only page 1 carries the name and period headers held in rows 1–3. Finance wants a PDF with those headers on every page, one page wide.
1. Why can nobody type, and what is the correct sequence for locking the template down?
Read the worked answer
Every cell is Locked by default (Format Cells > Protection > Locked), and Protect Sheet simply enforces that flag. Protecting without first unlocking the input area locks the whole grid.
Correct sequence:
- Select B4:H10 only > Ctrl+1 > Protection > clear Locked (clear Hidden too unless formulas must be concealed).
- Leave row 11 totals locked so they cannot be overwritten.
- Review > Protect Sheet, set a password, decide on 'Select locked cells' (usually allowed, so users can still read and copy) and leave 'Select unlocked cells' ticked.
- Test as a user before distributing.
If some staff need to add extra rows or columns, Review > Allow Edit Ranges names the range and can be password-free, avoiding a template-wide unlock. Note that sheet protection prevents accidents, not determined users - it is trivial to bypass. For confidentiality, use file encryption or an IRM service.
2. Pasted 15 escaped validation. Explain why, and design a control that actually holds.
Read the worked answer
Data Validation intercepts typing and drag-fill entry. Paste, fill from an unvalidated source, and programmatic writes overwrite the validated cell without triggering the rule - which is why 15 got in. Sheet protection does not close this: paste into an unlocked cell is permitted.
Layered control, in order of cost:
- Conditional formatting on B4:H10:
=OR(B4<0,B4>12)with a red fill and bold white text. Visible instantly, no macros. - Data > Circle Invalid Data before collection, so outliers are ringed on screen (and will print if circled).
- A collection check cell:
=SUMPRODUCT(--((B4:H10<0)+(B4:H10>12)>0))reporting 'entries out of range'; refuse the return until it reads 0. - Only if the risk justifies it, a Worksheet_Change macro that undoes out-of-range entries - requires .xlsm and a macro-trust policy across 60 machines, so usually overkill.
Recommend 1 + 3, and put the check cell next to the signature block so the approver sees it.
3. Set out the print/PDF setup so the header repeats and the whole week fits one page wide, including one caveat.
Read the worked answer
Steps:
- Page Layout > Print Area: A1:H11 (the used range only - an unset area may pull in stray formatted cells and add blank pages).
- Page Layout > Print Titles > Rows to repeat at top:
$1:$3. This must reference rows at the top of the print area; a header block placed mid-sheet will not repeat. - Scaling: Fit to 1 page wide by 1 page tall (set the tall box blank if you'd rather let it flow to a fixed number of pages).
- Insert manual page breaks if the grid must split at a specific day, then Print Preview to confirm nothing is clipped.
- Header/footer: page numbers as 'Page &P of &N', plus the staff name field if it must appear on each page.
- File > Export > Create PDF/XPS, or Save As > PDF, and open the result - do not trust the preview alone.
Caveat: Fit To shrinks the font to fit. With three pages collapsing to one, minimum text can drop below 8pt and become unreadable in print. Check at 100% zoom in the PDF; if it fails, switch to A4 landscape with narrower margins before accepting a smaller scale.