DEV Community: Construction Control

The Column You Type by Hand: What "Prior Completed $" Does to Your Pay-App Check

Construction Control — Fri, 30 Oct 2026 22:04:02 +0000

The problem: one hand-typed column feeds a check

Every month, the person assembling a subcontractor pay application works from three sets of records: T&M tickets, change order requests (CORs), and the pay-application schedule of values (SOV). In the Construction Control workbook, most of the SOV's structure is carried by the workbook itself. One column is not. Column D — "Prior completed $" — is typed in by hand, by you, for every line.

That hand-typed column matters more than it looks. The billed-to-date figure for each SOV line is built from it: the amount completed before this period, plus this period's billing. One of the workbook's seven checks, EX-07, compares each approved change order's billed-to-date total against its approved amount, and flags the line when the two differ by more than half a cent. EX-07 cannot see where the billed-to-date figure came from. If Column D is stale — last cycle's number, a figure typed from the wrong row, a value nobody rolled forward — the comparison starts from a wrong number, and the flag it produces (or fails to produce) is about your typing, not your billing.

A stale Column D cuts both ways. Understate it, and a fully billed, correct line flags as under-billed. Overstate it, and billed-to-date can land exactly on the approved amount and stay silent while real billing is still behind. Neither outcome tells you anything about the work. Both send you looking in the wrong place first.

The manual method: keep Column D current in three steps

Step 1 — Roll it forward right after each pay application goes out. When a pay app is submitted, take each SOV line's completed-to-date total from that submission and enter it as the new "Prior completed $" before the next cycle's billing starts. Column D should always describe the world as of the last submitted pay app — never the one before it.

Step 2 — Tie it out line by line before you run any check. Before relying on any billed-vs-approved comparison, read Column D against the totals on the last submitted pay application, line by line. You are looking for the two failures that actually happen: a line that was billed last cycle but never rolled forward, and a figure typed against the wrong line. This is a comparison of two documents you already have; it needs no software.

Step 3 — When a flag appears, check Column D first. An EX-07 flag means the approved amount and the billed-to-date total differ. Before questioning the billing, confirm the hand-typed input the total was built from. Column D is the only one of the three numbers in that comparison that a person typed from memory or from another document — check it before the other two.

A worked example (fictional)

The example below is fictional. The company, the change order, and the amounts were invented for this article, with round numbers. It is not data from a real job, and it is not one of the workbook's demo datasets.

Northgate Ceilings LLC (fictional) holds approved change order COR-21 for $9,000. Through last month's pay application, the full $9,000 had been billed — the line is complete, and nothing further will be billed against it.

This month, the office admin builds the new pay application. Column D for the COR-21 line still reads $6,500: the figure from two cycles ago, which nobody updated after last month's billing was entered elsewhere in the file. This period bills $0 on the line, so billed-to-date reads $6,500 + $0 = $6,500.

EX-07 compares billed-to-date ($6,500) against approved ($9,000). The difference is $2,500 — well over half a cent — and the line flags. Yet nothing about the billing is wrong: the work was billed in full last month. The flag is the stale cell. The admin updates Column D to $9,000, billed-to-date now reads $9,000, the difference is $0, and the flag clears.

Run the same error the other direction and the check goes quiet instead. If Column D had been typed as $9,000 while only $6,500 had actually been billed, billed-to-date would read $9,000, match the approved amount exactly, and produce no flag — with $2,500 of approved work still unbilled and nothing on the report pointing at it.

How to read an EX-07 flag when Column D was the cause

A flag is a difference, not a verdict. The check reports that two numbers disagree; it cannot say which number moved, and it cannot say any number is wrong. When EX-07 flags a line, read it in this order:

Column D for that line, against the last submitted pay application. Is the prior-completed figure the one that submission actually shows?
This period's billed amount on the SOV line. Was this cycle's billing entered once, on the right line?
The approved amount on the Change Orders sheet. Does it match the approved COR as recorded?

If the answer to the first question is no, you have found the usual cause: correct Column D, let the totals rebuild, and read the report again. If all three numbers check out and the difference remains, then the flag is pointing at a real billing difference — and it has earned a closer look on its own terms. (An earlier article in this series covered the EX-07 check itself — what a billed-vs-approved difference does and does not establish.)

What the workbook does — and does not do

The Construction Control workbook runs seven checks (EX-01 through EX-07) on the T&M tickets, change-order requests, and schedule-of-values records you enter in desktop Excel, and reports the exceptions it finds. EX-07 is the billed-vs-approved comparison described above.

It does not pull last month's figures from your accounting system, your files, or anywhere else. Column D is yours to type, and every check can only compare what is in the file in front of it. It does not update Column D for you, and it does not decide whether a flagged difference is an error — that judgment stays with the person who knows the job. It is a worksheet for use alongside your AIA G702/G703 — it does not replace them.

Where to get it

The workbook — Construction Control, the Subcontractor Pay Application Reconciliation Workbook — is currently paused and not available to buy while it is rebuilt and independently re-tested.

If you would rather run the seven checks by hand first, the free Pay App Pre-Submission Checklist is at payhip.com/b/ROCQb.

Change Order Log Check: The Approved Change Order That Never Reaches the Pay Application

Construction Control — Fri, 30 Oct 2026 18:28:46 +0000

Change Order Log Check: The Approved Change Order That Never Reaches the Pay Application

An approved change order can clear every signature and still never be billed — not because anyone rejected it, but because it never got a line on the pay application's Schedule of Values. The approval lives on the change order log. The billing lives on the schedule. The two lists are maintained separately, and a change order that exists on only one of them is work the contract has agreed to pay for that the billing structure has no place to charge.

Of the ways a change order loses money quietly, this is the quietest. Nothing is mispriced. Nothing is missing its backup. The line the money was supposed to travel on was never built.

Why approved work never reaches the schedule

The Schedule of Values is usually built at the start of the job, from the original contract scope. Change orders arrive afterward, one approval at a time, across the life of the project.

Adding an approved change order to the billing means a second, separate act: someone has to open the schedule and give it a line. That act has no deadline of its own. The approval is signed on a Tuesday; the schedule is updated when the next pay application is prepared, from memory, or from a list of recent approvals that may or may not be complete. A change order approved between applications, or approved while the billing staffer is working from last month's file, can be signed, filed, and fully documented — and absent from the schedule entirely.

Each list looks right read alone. The log shows the approval. The schedule shows every line it has. The gap only exists between the two lists, and it is found by asking one question of every approved change order: do you have a line?

Check it by hand

The manual version of the check is a comparison, and it is worth doing once even on a small job. Take the change order log and filter it to approved change orders only. Take the Schedule of Values and list every change order it carries a line for. Any approved change order on the first list that does not appear on the second is the defect — approved work with no billing line under it.

On a job with three change orders, that comparison takes minutes. On a job with forty, across several months of approvals and schedule revisions, it is the kind of comparison that gets postponed — and every month it is postponed, the missing line bills nothing, because a pay application can only bill against lines the schedule contains.

What the EX-06 check actually tests

EX-06 is the check for this problem in the Construction Control workbook, and its mechanism is a count.

For each change-order request whose status is Approved, the workbook counts how many lines on the pay application's Schedule of Values name that request. If the count is zero — the approved request appears nowhere on the schedule — the request is flagged, and the workbook's Exception Report counts the flagged requests so they can be reviewed as a set.

Here is the shape of it, with invented COR numbers and amounts for illustration:

COR	Status	Approved amount	Lines on the Schedule of Values	Flag
COR-401	Approved	$3,600	2	—
COR-402	Approved	$1,250	0	Flagged
COR-403	Approved	$980	1	—

COR-401 has lines and is not flagged. COR-402 is the defect: approved, priced, and absent from the schedule — there is no line to bill it on, this month or any month, until someone adds one. COR-403 is the row worth pausing on: it has a line and nothing billed against it yet, and EX-06 does not flag it. Whether the billed amount on a line that exists matches the approved amount is a different question, asked by a different check. EX-06 asks only whether the line is there.

The limits, stated plainly

EX-06 tests absence. It does not compare amounts, and it does not know why a line is missing.

A change order approved this week, with its schedule line deliberately scheduled for next month's update, is flagged exactly like one forgotten for a year. The flag cannot tell those apart, and it is not trying to: it is a worklist of approved change orders with no line, for a person to sort into "not yet" and "never added." The second kind is the one that matters, and it is invisible until the two lists are compared.

Only approved change orders are tested. A pending change order with no schedule line is not flagged — it has not been approved, so there is nothing yet that the schedule is failing to carry. And the check reads the pasted billing data only: if a line exists in the billing software but was never pasted into the workbook, the workbook cannot see it. A flag means the line is missing from the data the check can read — the fix starts by confirming where the line actually lives.

How EX-06 fits the seven

EX-06 is one of seven checks in the workbook. EX-01 counts duplicate ticket IDs by exact match. EX-02 flags tickets with Date, Scope, Labor hours, or Loaded $/hr blank — exactly those four fields. EX-03 flags a ticket with no change-order reference in its link column. EX-04 flags a change-order request whose linked tickets total $0 — a test of the ticket total, not of ticket existence. EX-05 compares a pending change-order request's amount against the total of its linked tickets — an amount comparison, not a status match. EX-07 flags an approved change-order request whose billed-to-date differs from its approved amount — and it only takes up requests that appear on the schedule at least once, which is why the missing-line case belongs to EX-06 and not to it.

Each check asks one narrow question. EX-06's question comes before billing can start: is there a line for this approved work at all?

Where to go from here

This one check runs free on the Construction Control site, on sample data — the EX-06 check page shows the flag on a request with no line and clears the ones that have lines:

https://ty-square-csv-preflt-2026-10.pages.dev/ex-06-check/?utm_source=devto&utm_medium=article&utm_campaign=evergreen&utm_content=article-7

The free pay-application checklist — the seven checks as a list you can work through by hand — is a separate listing, here:

https://payhip.com/b/ROCQb?utm_source=devto&utm_medium=article&utm_campaign=evergreen&utm_content=article-7

If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel workbook for desktop Excel with 528 formulas, including EX-06 and the six other checks named above. Data goes in by pasting from four CSV templates; there is no importer. It is a worksheet for use alongside your AIA G702/G703 — it does not replace them. The workbook listing is here: https://payhip.com/b/rXZ62?utm_source=devto&utm_medium=article&utm_campaign=evergreen&utm_content=article-7

Disclosure: this article was drafted with AI assistance. It is from Construction Control, maker of the workbook.

Change Order Log Check: When Billed-to-Date Does Not Match the Approved Amount

Construction Control — Fri, 30 Oct 2026 16:26:25 +0000

Change Order Log Check: When Billed-to-Date Does Not Match the Approved Amount

An approved change order has a number that was agreed, signed, and entered on the log. Months later, the billing against it has a number of its own — the billed-to-date — and the two numbers are not the same. The difference did not arrive in one step. It accumulated one pay application at a time, each one plausible on its own, and nobody ever put the approved figure and the billed total on the same line and subtracted.

That subtraction is the whole problem. Until it happens, an under-billed change order sits as work performed and approved but never fully charged, and an over-billed one sits as a question a reviewer will eventually ask with the packet already out of your hands.

How billed drifts away from approved

Billing against a change order rarely happens in one payment. A COR is approved, added to the Schedule of Values, and billed in pieces as the work progresses — a portion on this month's pay application, a portion on the next.

Each piece is entered separately, and each entry is a chance for the running total to wander. A line gets billed on two consecutive applications because last month's entry was not carried forward visibly. The approved amount was revised by a later signature, and the billing kept pace with the earlier figure. A final piece is billed at what the preparer remembers the balance to be instead of what the log says it is. Or the work is finished and fully billed, but one application's entry was keyed a digit off, and the error is small enough to ride along unnoticed.

None of these require a dramatic mistake. They require only that nobody compares the two totals directly — approved on one side, everything billed to date on the other.

What the EX-07 check actually does

EX-07 Billed ≠ Approved is the check for this problem in the Construction Control workbook, and its mechanism is one subtraction.

For each change-order request whose status is Approved and which has at least one billing line on the pay application's Schedule of Values, the workbook uses SUMIF to add every billed-to-date amount recorded against that request. Then it subtracts: approved amount minus billed-to-date total. If the difference is not zero — in either direction — the request is flagged, and the Exception Report counts the flagged requests so they can be reviewed as a set.

Here is the shape of it, with invented COR numbers and amounts for illustration:

COR	Approved	Billed to date	Difference	Flag
COR-301	$4,200	$1,400 + $1,400 + $1,400 across three pay apps = $4,200	$0	—
COR-302	$2,750	$2,200	$550 under	Flagged
COR-303	$1,600	$1,850	$250 over	Flagged

COR-301 was billed in three pieces, on three separate applications, and it is not flagged — the pieces sum to the approved amount, and a sum is all the check reads. COR-302 and COR-303 are flagged in opposite directions, and the check treats them identically, because to a subtraction, under and over are the same kind of fact: the two numbers do not match.

The limits, stated plainly

A difference is a flag to review. It is not proof of an error, and this check cannot tell you why the numbers differ.

The most common reason is innocent: the change order is still being billed. A COR approved last month and billed 80% to date shows a difference because the remaining 20% has not been billed yet — and it should not have been. The flag fires anyway. Read correctly, it is a prompt to confirm that the gap is the expected unfinished balance, not an accusation that the gap is wrong. When the final piece is billed and the totals meet, the flag clears on its own.

What the check does not see matters as much. An approved COR with no billing line on the Schedule of Values at all is not this check's case — a missing line is a different failure, flagged by EX-06; EX-07 only compares requests that appear on the schedule at least once. The comparison is amounts only: beyond the Approved status that admits a request to the check, no status is read, and a billed entry is never matched against anything but the approved figure. And the check reads the numbers as entered. If the approved amount itself was keyed wrong on the log, the subtraction faithfully compares billing against the wrong number, and a match would mean both sides repeat the same error. The check compares the data against itself; it cannot compare the data against the signed change order.

How EX-07 fits the seven

EX-07 is one of seven checks in the workbook. EX-01 counts duplicate ticket IDs by exact match. EX-02 flags tickets with Date, Scope, Labor hours, or Loaded $/hr blank — exactly those four fields. EX-03 flags a ticket with no change-order reference in its link column. EX-04 flags a change-order request whose linked tickets total $0 — a test of the ticket total, not of ticket existence. EX-05 compares a pending change-order request's amount against the total of its linked tickets — an amount comparison, not a status match. EX-06 flags an approved change-order request with no billing line on the pay application's Schedule of Values — absence only; it does not compare amounts.

EX-07 is the last of the seven because it asks the last question the packet can answer: for the work that was approved and billed, do the two totals agree?

Where to go from here

If you want the record-keeping structure first, the free guide to building a change order log template is here:

https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template

The free pay-application checklist is a separate listing, here:

https://payhip.com/b/ROCQb

If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel workbook for desktop Excel with 528 formulas, including EX-07 and the six other checks named above. Data goes in by pasting from four CSV templates; there is no importer. It is a worksheet for use alongside your AIA G702/G703 — it does not replace them.

Disclosure: this article was drafted with AI assistance. It is from Construction Control, maker of the workbook.

Change Order Form vs. Ticket Backup: When a COR's Linked Tickets Total $0

Construction Control — Fri, 30 Oct 2026 16:20:32 +0000

Change Order Form vs. Ticket Backup: When a COR's Linked Tickets Total $0

A change order request can sit on the log with an amount, a status, and a place in the billing — and have nothing standing behind it. No ticket amounts in the billing data add up under it. When a reviewer asks the standard question — show me the backup — the data cannot point anywhere, because in the pasted records, the backup is not there.

This is a different failure from a missing ticket. The change order side of the packet looks finished. It is the space underneath it that is empty.

Why a COR ends up with nothing behind it

Change order data and ticket data usually arrive by different roads. The COR is entered from the signed change order form. The tickets come from the field, in batches, through exports and pastes of their own.

The two sets meet only if a ticket's link entry names the COR it belongs to. That meeting can fail quietly. Tickets get pasted under a different reference than the one the COR carries. A batch of tickets for the work is still sitting in an inbox when the COR goes onto the log. Or the tickets are in the file, but their amounts stand at $0 while pricing is still being worked out — present as rows, absent as dollars.

Each set can look complete on its own. The COR list is tidy. The ticket list is long. Read together, one question exposes the gap: for this COR, what do its tickets add up to?

What the EX-04 check actually does

EX-04 COR Without Backup is the check for this problem in the Construction Control workbook. Its mechanism is a sum, and it can be stated in full.

For each change-order request, the workbook uses SUMIF to add the totals of every pasted ticket whose link entry names that request. Then it compares that sum against the request's own amount. If the linked tickets total $0 while the request's amount is not $0, the request is flagged ORPHANED_COR, and the Exception Report counts the flagged requests and totals their amounts.

Here is the shape of it, with invented COR numbers and amounts for illustration:

COR	COR amount	Tickets linked to it	Linked ticket total	Flag
COR-201	$1,850	3 tickets	$1,850	—
COR-202	$2,400	none in the data	$0	ORPHANED_COR
COR-203	$960	2 tickets, each $0	$0	ORPHANED_COR

COR-201 is what backup looks like in data: the tickets under it add up. COR-202 is the empty case — no ticket in the pasted data names it. COR-203 is the case worth pausing on, and it leads directly to what this check does and does not see.

The limits, stated plainly

EX-04 tests whether linked tickets total $0. It does not test whether tickets exist.

COR-203 above has tickets. They are linked. They are named in the data. The check flags the request anyway, because their total is $0 — and that is correct behavior for the question being asked, which is about amounts standing behind an amount. A flag on a COR with tickets is not the check misfiring; it is the check reporting that, in dollars, nothing is behind the request yet.

Two more edges, so the flag is never over-read. A change-order request whose own amount is $0 is not flagged by this check, with or without tickets — there is no amount asking for backup. And the check reads the pasted billing data only. Signed ticket copies in a project folder, a superintendent's binder, or an email thread are outside the workbook. An ORPHANED_COR flag means there is no backup in the billing data; it does not claim no backup exists anywhere. The flag tells you where to go look — it does not do the looking, and it cannot judge whether backup you find is signed, complete, or acceptable to the reviewer who asked for it.

Read that way, the flag is a worklist, not a verdict: these are the requests that will stall the moment someone asks for their tickets, while the packet is still in your hands.

How EX-04 fits the seven

EX-04 is one of seven checks in the workbook. EX-01 counts duplicate ticket IDs by exact match — and stated plainly, a ticket renumbered under a different ID counts once and is not flagged. EX-02 flags tickets with Date, Scope, Labor hours, or Loaded $/hr blank — exactly those four fields. EX-03 flags a ticket with no change-order reference in its link column. EX-05 compares a pending change-order request's amount against the total of its linked tickets — an amount comparison, not a status match. EX-06 flags an approved change-order request with no billing line on the pay application's Schedule of Values — absence only; it does not compare amounts. EX-07 flags an approved change-order request whose billed-to-date differs from its approved amount.

The checks divide the packet by the kind of question being asked. EX-04's question is the backup question: what stands behind this amount?

Where to go from here

If you want the record-keeping structure first, the free guide to building a change order log template is here:

https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template

The free pay-application checklist is a separate listing, here:

https://payhip.com/b/ROCQb

If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel workbook for desktop Excel with 528 formulas, including EX-04 and the six other checks named above. Data goes in by pasting from four CSV templates; there is no importer. It is a worksheet for use alongside your AIA G702/G703 — it does not replace them.

Disclosure: this article was drafted with AI assistance. It is from Construction Control, maker of the workbook.

Change Order Log Template: Four T&M Ticket Fields That Cannot Be Blank

Construction Control — Fri, 30 Oct 2026 16:19:51 +0000

Change Order Log Template: Four T&M Ticket Fields That Cannot Be Blank

A time-and-materials (T&M) ticket can be pasted into billing data looking complete and still be missing the one field a reviewer needs. The row has a ticket number. The description reads fine. But the rate column is blank — or the work has no date on it — and when the pay application is questioned, the ticket cannot answer for itself, because the missing piece was never in the data.

Blanks are quiet errors. A wrong number at least looks like something to check. A blank looks like nothing, and nothing is easy to scroll past.

Why fields go blank in paste-based billing

Ticket data rarely starts in the billing file. It starts on a paper ticket, a field app, an email, or an export from another system, and it gets pasted into the billing workbook in batches.

Each handoff can drop a field. The paper ticket records hours but the export does not carry the rate, because rates live in a different system. The scope note sits in the email body and never makes it into the pasted row. The date is on the ticket stub, and the person pasting works from the totals page instead.

Nobody has to be careless for this to happen. A paste can be internally tidy — columns aligned, totals present — with one field empty on one row, and the emptiness only becomes visible when someone asks that row a question it cannot answer: when was this work done? At what rate? For how many hours?

The four fields, and what a blank costs

Not every column on a ticket carries the same weight. Four of them carry the ticket's identity as a billable record:

Field	What goes in it	What a blank leaves behind
Date	The day the work was performed	A ticket that cannot be placed in a billing period or matched to a day's records
Scope	What work the ticket covers	Hours and charges with nothing they attach to
Labor hours	The hours worked	A rate with nothing to multiply; a quantity a reviewer cannot test
Loaded $/hr	The labor rate for those hours	Hours with no price; the ticket's labor amount cannot be derived or checked

A blank in any one of the four breaks a different link in the same chain. The date places the work. The scope names it. The hours size it. The rate prices it. Review backup works because a reviewer can walk that chain; a blank is where the walk stops.

What the EX-02 check actually tests

EX-02 Missing Fields is the check for this problem in the Construction Control workbook, and its scope is deliberately narrow: exactly those four fields, and no others.

For each pasted ticket row, the check asks four questions. Is the Date entry empty? Is the Scope entry empty? Is the Labor hours cell blank? Is the Loaded $/hr cell blank? If any one of the four answers is yes, the ticket is flagged MISSING_FIELDS, and the workbook's Exception Report counts the flagged tickets so they can be reviewed as a set instead of hunted one at a time.

That is the entire mechanism. The check reads the four cells and reports which rows come up empty. It does not read the rest of the row, and it does not grade what is written in the four fields — only whether something is there.

The limits, stated plainly

Four fields is a small net, and you should know exactly where its edges are.

EX-02 tests only Date, Scope, Labor hours, and Loaded $/hr. A blank anywhere else in the row is not this check's concern. A ticket with no change-order reference in its link column, for example, has all four fields present and passes EX-02 untouched — the missing link is a separate question, asked by EX-03, not by this one.

Filled in is not the same as correct. A rate typed in wrong is present, not blank, and this check does not flag it. An hours figure that does not match the paper ticket is present too. EX-02 finds empty cells; it cannot compare a cell against the field record it came from.

And the check reads the pasted data only. If the rate exists on a signed ticket in a job folder, the workbook cannot see it. A MISSING_FIELDS flag means the billing data has a hole in it — the fix is to fill the hole from the source record, and the flag is what tells you the hole is there before a reviewer finds it first.

How EX-02 fits the seven

EX-02 is one of seven checks in the workbook. EX-01 counts duplicate ticket IDs by exact match. EX-03 flags tickets with no change-order link. EX-04 flags a change-order request whose linked tickets total $0. EX-05 compares a pending change-order request's amount against the total of its linked tickets. EX-06 flags an approved change-order request with no billing line on the pay application's Schedule of Values — absence only; it does not compare amounts. EX-07 flags an approved change-order request whose billed-to-date differs from its approved amount.

Each check asks one narrow question. EX-02's question is the simplest of the seven: is anything missing?

Where to go from here

If you want the record-keeping structure first, the free guide to building a change order log template is here:

https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template

The free pay-application checklist is a separate listing, here:

https://payhip.com/b/ROCQb

If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel workbook for desktop Excel with 528 formulas, including EX-02 and the six other checks named above. Data goes in by pasting from four CSV templates; there is no importer. It is a worksheet for use alongside your AIA G702/G703 — it does not replace them.

Disclosure: this article was drafted with AI assistance. It is from Construction Control, maker of the workbook.

Unlinked T&M Tickets: Work That Is Done But Connected To Nothing

Construction Control — Fri, 30 Oct 2026 12:11:28 +0000

A time-and-materials (T&M) ticket can describe real work, in full, and still go nowhere on a pay application. The labor is recorded. The work was done. Yet that ticket is not connected to the change order work it belongs with, and it is not carried into the billing in a way a reviewer can follow. It is present as a row of data, and stranded at the same time.

That is the problem EX-03, Unlinked Tickets, is about. This article explains that check in plain language: what a link is, why a ticket loses it, why a careful reader can miss the problem, and where EX-03 sits in a set of seven checks.

What a link does for a ticket

A ticket on its own is a record of work. A link is what places that record inside the rest of the packet. It ties the ticket to the change order side of the job, so the work on the ticket can be followed into the amounts put forward for billing.

With that connection, a reviewer can move from the ticket to the related change order record, and from there toward the pay application, and see that the pieces belong together.

Without it, the chain stops at the ticket itself. The ticket may be filled in, but there is nothing in the packet that it is attached to. It leaves the billing admin with a question the ticket alone cannot answer: where does this work belong, and under what was it approved to be billed?

Why tickets become unlinked

In paste-based billing, ticket data, change order data, and pay application data are handled as separate sets. The connection between them has to survive every paste, sort, and update between the field record and the finished packet.

A ticket can be pasted into a billing file while the change order data it refers to is pasted elsewhere, or is not pasted in at all. A ticket can be added late, after the change order list for that cycle has been assembled, so the new row arrives without the record it should sit under. A reference can be carried in a form that does not match the related record in the other set, so the two records sit in the same packet and still do not connect.

None of those steps has to look like a mistake when it happens. Each paste can look complete on its own. The missing connection becomes visible only when the sets are read together, as one packet, and each ticket is asked whether it is tied to something else in that packet.

Why reading the ticket does not reveal it

A person reviewing a ticket reads its content: the work description, the labor, the materials, and the fields that describe the work. Read that way, an unlinked ticket can look entirely ordinary. Nothing inside the row has to look wrong for the connection outside the row to be missing.

That is the central point of EX-03. The fault is not inside the ticket. The fault is between the ticket and everything else. A review that stays inside each record can pass over a missing connection again and again, because the thing that is missing is a relationship, not a word or a number in the row.

The reader asks what the ticket says. A check for unlinked tickets asks what the ticket is connected to. A packet can pass the first question and fail the second.

Where EX-03 sits in the seven checks

EX-03 is one of seven checks in the Construction Control workbook:

EX-01 Duplicate Tickets. A COUNTIF check on ticket IDs. If the same ticket ID appears more than once, the count shows it. Stated plainly: a renumbered ticket is NOT caught by this check, because a renumbered ticket carries a different ID.

EX-02 Missing Fields. An ISBLANK check, which looks for blank fields in the pasted data.

EX-03 Unlinked Tickets. The check covered in this article, which looks at tickets that are not connected to the rest of the packet.

EX-04 COR Without Backup. A SUMIF and ABS check that works with an ORPHANED_COR result, for change order records without backup behind them.

EX-05 is a totals comparison. It uses SUMIF totals. It is NOT a status match.

EX-06 Missing from Pay App. The billing gap check, built on Approved amounts and ABS variance, for approved work that is missing from the pay application.

EX-07 Billed Not Equal Approved. A check for amounts billed that are not equal to the amounts approved.

The separation matters. A blank field is EX-02, not EX-03. A duplicate ID is EX-01, not EX-03. A totals difference is EX-05, and a billed amount that differs from an approved amount is EX-07. EX-03 keeps its attention on one narrow thing: the connection, or the missing connection, between a ticket and the packet around it.

EX-03 is about linkage in the pasted data, as the data stands in the workbook. It does not decide whether work was performed well, whether a change should have been directed, or whether a reviewer will accept a packet.

How the workbook is used

The Construction Control workbook is $39. It is a macro-free workbook for desktop Excel. Data is brought in by pasting, using 4 CSV templates. There is no importer.

If you want to work on the record-keeping side first, start with the free guide to a change order log template:

https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template

A free checklist PDF is linked from the guide.

A ticket that records finished work but is connected to nothing is hard to bill from a packet. Putting the connection question on a checklist, as its own separate question, is how that stranded row gets found while the packet is still in your hands.

This is from Construction Control, maker of the workbook.

Duplicate T&M Ticket Numbers: The Pay-App Error Hiding in Plain Sight

Construction Control — Fri, 30 Oct 2026 08:06:57 +0000

Most pay-application errors announce themselves. A formula breaks, a column shifts, a total doesn't tie out — something looks wrong, so somebody looks at it. A duplicate time-and-materials (T&M) ticket does the opposite. It sits inside a block of pasted ticket data that looks perfectly healthy, and it survives every review that reads tickets without counting them.

Here's the shape of the failure — no names, no dollar figures, because it doesn't need any. The same T&M ticket gets pasted into two billing cycles. On the subcontractor's side, nothing looks off: the paste goes in, the backup gets attached, the pay application goes out. Then the general contractor's reviewer runs a pass over the tickets, finds the same ticket number in two places, and flags it. The pay app stalls while the backup gets dug up and re-proved — for work that was already billed once.

The painful part is rarely the duplicate itself. It's the stall.

Why paste-based billing produces duplicates

If your billing runs on pasted data, duplicates are a structural risk, not a carelessness problem.

The billing-cycle boundary is the classic spot. Last cycle's tickets are still sitting in the file or the export when this cycle's batch gets pasted in, and the two ranges overlap. Nobody types the ticket twice in one sitting — it crosses a boundary, so each paste looks complete on its own.

Ticket data also arrives from more than one place. Field emails, PDF exports, a superintendent's spreadsheet: the same ticket can show up in two batches, and neither batch looks partial.

And eyes don't catch it, because duplicate rows don't look wrong. The columns line up. The labor and equipment entries are real. Inside a block of valid-looking rows, a duplicate reads as more work, not as an error.

A person reviewing tickets reads their content. A duplicate check reads their IDs. That's the whole difference, and it's why counting catches what reading doesn't.

What a COUNTIF check actually sees

EX-01 is the duplicate check in the Construction Control workbook. It counts duplicate ticket IDs with a COUNTIF on the ticket ID column of the pasted T&M ticket data, and the mechanics are simple enough to explain in full.

COUNTIF takes a range and a criterion, and returns how many cells in the range match it. For a five-row example, the formula next to the first ticket ID looks like this:

=COUNTIF($A$2:$A$6, A2)

Copied down the column, every row asks the same question: how many times does this exact ID appear in the column? In a real paste block, the range simply runs to the last row of data.

Here's the worked example, using invented ticket numbers for illustration:

Ticket ID	COUNTIF result
TM-1041	1
TM-1042	2
TM-1043	1
TM-1042	2
TM-1044	1

Rows two and four hold the same ticket, pasted into two cycles — the failure described above. Every ID counts 1 except TM-1042, which counts 2 in both places. That's the entire mechanism.

Notice what the check is doing. It doesn't understand tickets, labor, billing cycles, or backup. It compares values in one column and counts them. That makes it exhaustive on exactly one question — does this exact ticket ID appear more than once? — and blind to everything else, including one case you should know about before you rely on it.

The limitation, stated plainly

EX-01 catches the same ticket number entered twice. It does not catch a ticket that was renumbered.

If a ticket goes in once as TM-1042 and the same work goes in again under a reissued number — TM-1099, say — the column holds two different values. COUNTIF counts each of them once. Nothing flags, because nothing matches.

No formula reading the ticket ID column alone can catch that case; to an ID comparison, a renumbered duplicate looks like two legitimate tickets. That's why the plain description matters more than a flattering one. A check you understand is a check you can lean on for the errors it actually sees — and supplement for the ones it doesn't. You're not handing your judgment to a formula; you're using it for the one mechanical question it answers completely.

How the check fits the seven

EX-01 is one of seven checks in the workbook. The other six look at missing fields, unlinked tickets, change orders without backup, a totals comparison, the approved-but-not-billed gap, and billed ≠ approved.

The duplicate count is the most mechanical question in the set — one column, one count — and the one a reading reviewer is least equipped to answer by eye. Seven small questions, each aimed at one specific way paste-based billing data goes wrong.

Where to go from here

If you want to get the underlying process in order first, start with the free change-order log template guide:

https://ty-square-csv-preflt-2026-10.pages.dev/articles/change-order-log-template

A free checklist PDF is linked from the guide.

If you want the checks themselves, the Construction Control workbook is $39 — a macro-free Excel file with 528 formulas, including EX-01 and the six other checks named above.

Disclosure: this article was drafted with AI assistance.

Construction Change Order Log Template | Free Guide

Construction Control — Fri, 30 Oct 2026 00:45:58 +0000

A pay application draws on three kinds of records: field tickets, change orders, and the schedule of values. Each kind is usually kept on its own, so a ticket can appear twice, a change order can lack backup, or a billed amount can differ from an approved amount. Before the application goes out, those records must agree with each other.

I built Construction Control, an Excel workbook (v1.1.2) for subcontractor pay-application review. It is macro-free and requires desktop Excel. Its 528 formulas use nine functions only: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. Users paste data into four CSV templates for tickets, change orders, SOV main, and SOV retainage, running seven checks.

This article explains the design of those seven checks and the formula approach behind them, including why no lookup function is used. It also states the limits plainly: there is no importer, the workbook runs the checks, and the human reconciles. A separate free one-page checklist PDF exists, but it runs no checks.

Pasting records in

Records enter the workbook only by pasting, not by file import. The workbook ships with four CSV paste templates: template_tickets.csv, template_change_orders.csv, template_sov_main.csv, and template_sov_retainage.csv. The buyer fills a template from their existing spreadsheet or accounting export, then pastes the values into the workbook at marked starting cells. A paste guide (PDF) shows the exact cells, the formats, and a count check to run before trusting a paste. The workbook does not import files by itself and does not connect to any accounting system. Records that exist only on paper, in texts, or in PDFs still have to be typed once, into the templates.

About this article

This article explains how Construction Control — Subcontractor Pay Application Reconciliation Workbook runs its seven exception checks on records pasted into it, gathers flagged items in an Exception Report, and how records move through them; after this first mention, it is called "the workbook".

The formula approach

The workbook's formulas use exactly nine Excel functions: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. There is no lookup function in the workbook. Across the seven exception checks, the formulas count occurrences, sum values by key over the pasted ranges, and test for blanks.

A worked example from the demo sheet

The workbook's Metro Tower Demo sheet includes COR-03, an approved change order for a credit of $390 (signed value −$390). Nothing is billed for it on the pay application: EX-06 compares the approved value against the billed total accumulated by SUMIF — $0 here — and flags the gap. The Exception Report is a summary table (Check ID, Review category, Count, Signed amount $, Status, Interpretation); it never lists individual change orders. COR-03 appears only inside the EX-06 row: count 1, signed amount −$390. The report's own notes state that amounts across categories overlap and must never be summed, that a missing credit is not unclaimed revenue, and that a flag means a comparison needs human attention — the workbook runs the checks, the human reconciles.

The seven checks, one by one

All seven checks run on pasted records. EX-01 Duplicate Tickets uses COUNTIF to count rows with the same Ticket ID; an ID on multiple rows is flagged. EX-02 Missing Fields blank-tests each ticket's Date, Scope, Labor hours, Loaded $/hr; a ticket missing any is flagged. EX-03 Unlinked Tickets flags a ticket with no COR ID in its Linked COR column.

EX-04 COR Without Backup uses SUMIF to total linked tickets for each change-order request; a $0 total is flagged for human review, since some legitimate credits have no field ticket, so it is not an error verdict. EX-05 Pending COR Mismatch flags a pending request whose proposed amount differs from its linked tickets' SUMIF total. EX-06 Missing from Pay App uses SUMIF to accumulate an approved request's billed total from pay-application records; the gap between approved and billed totals is flagged. EX-07 Billed ≠ Approved flags an approved request on the pay app whose billed-to-date total differs from its approved amount, over-billed included. Flagged items form one Exception Report, a summary table never listing individual change orders.

Build Notes: Four Constraints That Shaped the Design

Desktop Excel is required, and the workbook contains no VBA and no macros of any kind. That constraint keeps every calculation visible as a cell formula rather than hidden code. A reviewer can inspect a result, trace its precedents, and see exactly which cells produced it, without trusting a script.

Data enters only by pasting into one of four CSV templates: tickets, change orders, SOV main, and SOV retainage. There is no importer, no file-open dialog, and no external data connection. That limit puts the pasted ranges in fixed places, so the formulas can address the same ranges on every review.

The workbook uses 528 formulas from nine functions only: IF, ROUND, ABS, AND, OR, COUNTIF, SUMIF, ISBLANK, and SUM. It deliberately uses no lookup function: no VLOOKUP, no INDEX/MATCH, no XLOOKUP. Cross-list comparisons count and sum by key over the pasted ranges, matching by shared keys instead of pulling values from another list.

The seven checks, EX-01 through EX-07, produce flags for human review, and the Exception Report gathers them in a summary table. The workbook runs the checks and stops there. It does not decide which flag is correct, change a pasted value, or close an exception. That decision stays with the reviewer.

What the workbook does not do

The workbook does not import files by itself and does not connect to any accounting system. It compares only the records the buyer enters; it does not verify those records against the contract, owner documents, or signed tickets. It does not flag billing over 100% of scheduled value. It does not check retainage rates or G702/G703 totals. It does not flag change orders billed before approval. A pending change-order request that has already been billed may not be identified by any of the seven checks. The "Prior completed $" column on the pay-app sheet is typed in by hand. It does not file or submit the pay application. It does not approve anything, and it does not guarantee a pay application will be accepted or paid.

The workbook runs seven exception checks on pasted records, gathering flagged items in the Exception Report, a summary table never listing individual change orders. A flag needs human attention; the workbook checks, the human reconciles. The checks compare only entered records, not the contract, owner documents, or signed tickets, and do not check G702/G703 totals, retainage rates, or billing beyond the full scheduled value. It is a desktop Excel workbook that does not file or submit the pay application.

Resource mentioned in this article

The workbook described above is Construction Control — Subcontractor Pay Application Reconciliation Workbook (v1.1.2), a paid Excel workbook from Construction Control. The free guide this article accompanies is published in full at the canonical link for this post, and the one-page checklist PDF mentioned above is free and runs no checks.

Disclosure: AI tools assisted in drafting this article. The publisher reviewed and verified its contents before publication.