Site quality register, a macro-enabled 'tender' email and an overwritten shared file
You are the junior engineer maintaining the quality register for a 12-storey site. The register is an Excel workbook with 48 data rows: column A = date of pour, B = batch number, C = grade (M20 or M25), D = 28-day cube strength in N/mm², E = remarks. Your senior asks how many M25 results fell below 22 N/mm² and what the average M25 strength is, and wants the same formulas to keep working when new rows are added and when the sheet is sorted by date. Separately, an email arrives with the subject 'URGENT: Tender NIT-47 revised BOQ', a sender display name matching a known contractor but a domain ending in '.info', and an attachment 'BOQ_revised.xlsm' that asks you to 'Enable Content'. A colleague has already opened it and says 'nothing happened'. Later you find that the shared register on the office desktop has been overwritten by an older copy carried on a pen drive.
1. Write the correct formulas for the two required figures and explain how they stay correct when rows are added, copied or sorted.
Read the worked answer
Count of M25 results below 22:
=COUNTIFS($C$2:$C$49,"M25",$D$2:$D$49,"<22")
Average M25 strength:
=AVERAGEIF($C$2:$C$49,"M25",$D$2:$D$49)
Points of reasoning:
- The comparison is written as the text string
"<22", not as a number; COUNTIFS interprets it as an operator criterion. - The
$signs lock the ranges so that copying the formula down or across does not shift them onto the wrong rows. - COUNTIFS (plural) is needed because there are two conditions; COUNTIF accepts only one.
- Sorting by date does not break these formulas because they evaluate whole ranges by criterion, not by row position. A VLOOKUP with approximate match, or a formula referring to a fixed row such as D5, would break after sorting.
For a growing register, convert the range to an Excel Table (Ctrl+T) and use structured references, e.g. =COUNTIFS(Table1[Grade],"M25",Table1[Strength],"<22"), so new rows are included automatically.
2. The colleague opened the .xlsm attachment. What is your assessment and what should be done now?
Read the worked answer
Treat this as a phishing attempt with a possible macro malware payload. The indicators are: urgency in the subject line, a display name that does not match the actual '.info' domain, an unexpected revision of a tender document, and a macro-enabled workbook that explicitly asks for 'Enable Content'. Macros are code; once enabled they can download a payload, encrypt or exfiltrate files, or modify the register.
Actions:
- Do not enable content, and disconnect the colleague's machine from the network if it is still open.
- Run a full antivirus/EDR scan and have IT check startup items, recent files and outgoing connections.
- Verify the tender independently: call the contractor on a number from the official tender document, and check the tender portal - never reply to the email or use its links.
- Report the message to IT/security and delete it from the mailbox after saving the header for investigation.
- Remind the site office that genuine BOQs should come through the official portal, and that macro-enabled files from external senders are blocked by default.
3. How would you restore and protect the overwritten register?
Read the worked answer
First recover content, then fix the process.
Recovery:
- Use File > Info > Version History (OneDrive/SharePoint) or right-click the file > Restore previous versions to recover the last good copy.
- Check the recycle bin and any AutoSave/AutoRecover drafts.
- Compare the restored file against independent evidence - pour cards and lab reports - and verify the row count and a checksum such as total number of results and average strength before accepting it.
Protection:
- Keep the register on SharePoint/OneDrive with version history rather than a loose desktop file.
- Set permissions so that only the quality team can edit, and use check-out for edits.
- Keep dated backups and never circulate the master by pen drive; pen drives are both a malware vector and a version-control hazard.
- Hiding sheets or marking a file 'final' is not protection; it does not stop overwriting.