◆ Data & Lookups · drill · Spreadsheet Formulas & Data track
Two systems, one truth
a recon asks two questions, not one — is the deal there, and is it the same number; the check cell answers both at once.
Month-end, and the two systems disagree. The trading blotter is the system of record; the finance extract is what actually got booked — one deal never made it across at all, and one that did was booked at the wrong figure. Work the recon: mark which blotter deals finance is carrying, add the missing one, price every booked line against the blotter, repair the break, and finish with the Recon check reading zero. Nothing goes out while the recon still shows a difference.
The shortcuts in this drill
Ctrl+D⌘D on macFill down from the cell above
Build the logic once in the top cell, select down, and stamp it — the standard way to fill a schedule.
Ctrl+C⌘C on macCopy
Copy starts almost every hand-off — grab a finished block once, then place it in the summary, the deck, or next quarter’s column.
Ctrl+V⌘V on macPaste
Plain paste brings everything: values, formulas, formats. Reach for Paste Special when you only want part of it.
functions you'll type: COUNTIF() · INDEX() · MATCH() · SUM()
The optimal line
the par-setting sequence, straight through:
C4 =COUNTIF($E$4:$E$10,A4) · fill C4:C10 · copy A10:B10 → paste E10 · G4 =F4-INDEX($B$4:$B$10,MATCH(E4,$A$4:$A$10,0)) · fill G4:G10 · retype the odd amount · G11 =SUM(G4:G10) · ctrl+s
More Data & Lookups drills