---
title: "Dead references in copy-pasted offer tabs"
description: "Copying a tab for the next offer leaves formulas pointing at rows that are gone. The errors that show are the mild case; the ones guarded into zeros get used."
url: https://offersuite.io/articles/dead-references-in-copied-tabs
updated: 2026-10-13
---

# Dead references in copy-pasted offer tabs

Copying a tab for the next offer leaves formulas pointing at rows that are gone. The errors that show are the mild case; the ones guarded into zeros get used.

Spreadsheet mistake 6 of 9

## The mistake

A workbook grows by copying. The subscription tab is duplicated for a one-time offer, the rows that don't apply are deleted, and every formula that pointed at them now reads #REF!. Cells that divided by a renewal margin read #DIV/0!. Lookups into a cost table that was renamed read #N/A.

The errors that show are the mild case, because someone sees them. The costly ones are hidden: an error wrapped in a guard that turns it into 0, or a cell on a tab nobody opens that a summary still adds up.

## A worked example

Three coffee offers, one tab each. On the first order the subscription keeps $0.20, a single bag bought once keeps $20.61, and three bags bought once keep $55.45.

The three-bag tab was copied from the subscription tab, and its margin cell lost a reference. The summary tab guards against errors, so it reads that cell as 0. Its average first-order margin across the three offers is $6.94, where the offers average $25.42.

The single-bag tab kept the "renewals to break even" cell. With $35.00 spent to acquire the customer, the first order leaves $14.39 unrecovered, and there is no renewal margin to divide it by. Unguarded, the cell reads #DIV/0!. Guarded, it reads 0, which a reader takes to mean the offer pays back at once.

First-order margin of three offers, and what the summary tab reads

| Offer | First-order margin | What the summary reads |
| --- | --- | --- |
| Subscription, first order | $0.20 | $0.20 |
| One bag, bought once | $20.61 | $20.61 |
| Three bags, bought once | $55.45 | $0.00 |
| Average of the three | $25.42 | $6.94 |

The order's figures are computed by the margin engine in Offer Suite's own code, in whole cents. The sheet's are the same order with the mistake applied.

## What it does to the decision

A zero is a number, and it gets used. The summary's average drags down a set of offers that are fine, and the offer missing from it is the best of the three. In the other direction, a break-even of 0 renewals says that an offer which never recovers its acquisition cost has nothing to recover.

Once a workbook has a few of these, people stop trusting any tab they didn't build themselves, and each new offer starts from one more copy.

## What prevents it

Offer Suite has nothing to copy between offers. Each offer is its own validated [document](https://offersuite.io/features/offer-documents), and its figures are computed from it by one engine, so one offer can't point at a row of another.

Where something is missing, the answer says so instead of falling back to zero. A [forecast](https://offersuite.io/features/profit) lists the required costs no line has an amount for yet, because the margin is overstated until they are entered. Payback on an option with no renewals, or whose renewals earn nothing, comes back as never.

A spreadsheet is still the right tool for some of this. Where it is, and where it stops: [Offer Suite vs a spreadsheet](https://offersuite.io/compare/spreadsheets).

## Where the numbers come from

The example is not a real brand's offer. The acquisition cost of $35.00 is made up. Every other input is sourced below, or marked there as an assumption.

|  |  |
| --- | --- |
| Price | $40.00, a bag of coffee. Offer Suite's dev store catalog |
| Unit cost | $12.00. Offer Suite's dev store catalog |
| Refund reserve | 5%, Offer Suite's default assumption for consumables. NRF's all-category figure for online sales is 19.3%, but consumables are rarely returned; use your own rate. |
| Pick and pack | $3.20 per order and $0.48 for each extra item. [Fulfill.com, 3PL pricing benchmarks (The Fulfillment Advisor 2025 survey of 600+ warehouses), read 23 September 2026](https://www.fulfill.com/3pl-pricing) |
| Shipping label | USPS Ground Advantage, commercial, Zone 5, by weight rounded up to the pound: 1 lb $9.29 · 2 lb $10.50 · 3 lb $12.12 · 4 lb $13.89 · 5 lb $14.53. Packed weight is an assumption: 0.8 lb per unit. [USPS Notice 123, USPS Ground Advantage Commercial, Zone 5 (effective 4 October 2026), read 13 October 2026](https://pe.usps.com/text/dmm300/Notice123.htm) |
| Card fees | 2.9% + $0.30 per order: Shopify Payments' online rate for a standard card on the Basic plan in the United States. Higher plans pay a lower rate, and premium cards a higher one. [Shopify's pricing page for the United States, online standard card rates, read 12 October 2026](https://www.shopify.com/pricing) [Rates by plan and country](https://offersuite.io/fees/shopify-payments/united-states) |

## Related

- [Articles **Spreadsheet mistakes that misprice offers** Nine mistakes that turn up in offer spreadsheets, each worked on a sourced order: what the sheet shows, what the order earns, and what prevents the error.](https://offersuite.io/articles)
- [Calculator **Subscription payback calculator: CAC and churn** How much a subscriber's first order loses against what you paid to acquire them, how many renewals it takes to earn that back with churn, and in which month.](https://offersuite.io/calculators/subscription-payback)
- [Calculator **Offer option calculator: profit per visitor** Compare up to three offer options on profit per visitor, margin per order times conversion, and see the conversion lift each one needs to match the best.](https://offersuite.io/calculators/offer-option-profit-per-visitor)
- [Comparison **Offer profit in a spreadsheet vs Offer Suite** Where a spreadsheet is the right tool for offer profit, and where it breaks: costs that change, renewals, refunds, and a price that has to match checkout.](https://offersuite.io/compare/spreadsheets)
- [Article **Margin computed on a price that includes VAT** Where shelf prices include VAT or GST, margin worked out on the shelf price counts the tax as money kept. The tax comes almost entirely out of the real margin.](https://offersuite.io/articles/margin-on-a-price-that-includes-vat)
- [Feature **Offer documents: one validated document per offer** An offer is one validated, versioned document your agent writes: tiers, bundles, subscriptions, gifts and prices, with no offer types to pick from.](https://offersuite.io/features/offer-documents)