Consolidating twelve panchayat workbooks, fitting four printed pages, and a bounced 14 MB PDF
A block development office compiles a monthly progress report from 12 gram panchayats. Each panchayat emails a workbook, but the single data sheet inside is named inconsistently: 'Jan', 'Jan ' (trailing space), 'January' and 'jan'. Figures arrive sometimes as numbers and sometimes as text with trailing spaces. The clerk uses a fixed formula, =VLOOKUP(A2,Master!$A$2:$C$50,2,FALSE), to pull the panchayat name and officer, and on three rows it returns #N/A even though the panchayat is clearly present in the master list. The consolidated sheet prints seven pages wide on the office's A4 laser printer, but the district office accepts at most four pages and insists that the report title appear on every page. When the clerk emails the finished 14 MB PDF, it bounces with 'message size exceeds the fixed limit', although the block office's own sending limit is 25 MB; the same mail to a second office with a 10 MB limit also bounced. The clerk has no cloud storage account.
1. Explain why the three rows return #N/A and give a robust formula, including how to handle the inconsistently named sheets during consolidation.
Read the worked answer
#N/A with the FALSE argument means the lookup value was not found exactly. The panchayat is in the master list, so the values differ invisibly. The usual causes: trailing or leading spaces introduced by copy-paste; a non-breaking space (CHAR 160) pasted from a web page, which TRIM does not remove; a number stored as text (left-aligned cell, green triangle) being looked up against a numeric column; or a name typed with a different separator or abbreviation.
Fix the data first, then harden the formula: =IFERROR(VLOOKUP(TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),"")),Master!$A$2:$C$50,2,FALSE),"NOT FOUND - check name") Using IFERROR to display a flag rather than a blank keeps errors visible instead of hiding them. A left-aligned id is a clue to convert text to numbers with Text to Columns or VALUE().
For the sheets, do not hand-write twelve references. Build a list of sheet names in a column and use INDIRECT("'" & B2 & "'!C10"), where the surrounding single quotes make names with trailing spaces resolve; note that INDIRECT is volatile and breaks if a sheet is renamed. The durable answer is to issue a locked template to the panchayats so every workbook uses one sheet name and one column layout, after which Data > Consolidate or Power Query merges all twelve without formulas at all.
2. The report must print on four A4 pages with the title on every page. Give the exact steps and warn where the obvious approach fails.
Read the worked answer
Seven pages wide means the columns overflow, not the rows: the sheet is printing portrait at 100% with default margins and probably no defined print area, so irrelevant columns are being carried onto extra pages.
Steps: set Page Layout > Print Area to exclude helper and notes columns; set Orientation to Landscape; set Margins to Narrow; then Page Layout > Scale to Fit and enter Width: 1 page, Height: 4 pages. Set Print Titles > Rows to repeat to $1:$2 so the report title and column headings repeat on each page, and add a footer with page numbers.
The trap is that fitting width 1 on a sheet that was seven pages wide shrinks the text to roughly 14% of normal, which is unreadable and will be rejected just as surely as seven pages. So shrink the data before scaling: hide or group detail columns, move long notes into a legend or an appendix, shorten column widths and headers, drop to a 9-10 pt font, and put totals in a compact block rather than a spare column.
Finally, export to PDF and count the pages before emailing. The PDF, not the spreadsheet's preview, is what the district office will see.
3. Explain why the email bounced even though the block office's own limit is 25 MB, and rank the practical delivery options.
Read the worked answer
The receiving server's limit is the binding one, and it applies after encoding. SMTP carries attachments as base64, which inflates size by about one third: a 14 MB PDF becomes roughly 18.6-18.7 MB on the wire, plus headers and the message body. That clears neither a 10 MB limit nor, in practice, a 15 MB one, and it is why both recipients rejected it while the sender's 25 MB cap was irrelevant. Base64 inflation is also why a file described as 14 MB may be counted as nearly 19 MB by the recipient's quota.
Options, best first:
- Upload the PDF to a cloud drive (Google Drive, OneDrive, or the district's own portal), set view-only sharing, and email the link. This also avoids filling the recipient's mailbox and gives an audit trail of when it was opened.
- Reduce the PDF itself to about 7 MB or less, so it encodes below a 10 MB limit: re-export with compressed images and downsampled scans, embed fewer fonts, or export only the tables rather than the whole workbook range.
- Split the PDF by page range into two messages, labelling them part 1 and part 2.
Do not simply zip the PDF. PDF is already compressed, so the saving is negligible, and many mail gateways block .zip attachments outright.