Most spreadsheet problems don't announce themselves. They sit quietly for months — a customer's phone number typed slightly differently on three different rows, or a product price that was correct back when the order was placed but is now wrong everywhere because someone updated a master price list. Nobody notices until a report doesn't add up.

In this tutorial, we'll look at exactly how that goes wrong, rebuild the same scenario using proper relational linking, and then add one more layer most people miss entirely — locking a price in place at the exact moment a purchase happens, so it survives future price changes untouched.

---

The Problem, First

Here's what this often looks like in a spreadsheet — an Orders sheet with the customer's name and phone number retyped into every single row, alongside whatever the product's price happened to be at that time.

A messy spreadsheet showing repeated customer phone numbers and product prices copied into every order row

Look closely at Priya Nair — her phone number appears three times, once with a typo. And look at the Ceramic Mug — priced at $18 on her first order, but $22 on a later one, because someone updated the master price list in between and there was no way for the old rows to know that wasn't supposed to change them retroactively... except it did, because whoever built this sheet re-pulled the price with a formula that pointed at the current price, not the price paid.

This is the exact failure mode we're going to eliminate — twice.

---

Table Columns

Here's what we're rebuilding with — a Customer table linked to an Order table through a One-to-Many relationship, with each order linked to a Product.

• Customer — Name, Phone
• Order — Customer (linked), Product (linked), Quantity, Date
• Product — Name, Price

---

Step 1: Add Your Customers, Products, and Orders

Add a customer, a couple of products, and a few orders linking them together.

Customer table with Priya Nair's record

Product table showing the Ceramic Mug priced at 18 dollars

Order table showing orders linked to Priya Nair and the Ceramic Mug

For this walkthrough, assume:

• Ceramic Mug — currently priced at $18
• Priya Nair places an order for 2 units on September 2, 2026

Notice that Priya's phone number lives in exactly one place now — her Customer record — and her order simply links to her, instead of retyping anything.

---

Step 2: Pull the Price with Lookup — and See the Blind Spot

On the Order table, add a Lookup field pulling Price from the linked Product.

Adding a Lookup field on the Order table pulling Price from the linked Product

Priya's order shows $18, correctly, pulled live from the Ceramic Mug's current price.

Order record showing the looked-up price of 18 dollars

Now suppose the Ceramic Mug's price increases to $22 the following month.

Updating the Ceramic Mug's Price from 18 to 22 dollars

Go back and check Priya's September order.

Priya's order's Looked-Up Price changes from $18 → $22, silently, even though she paid $18.

Priya's original order now incorrectly showing 22 dollars after the product price was updated

---

Step 3: Fix It With a Snapshot

Delete that Lookup field, and instead add a Custom Logic column from the start, since a plain or Lookup field can't be converted into Custom Logic after the fact.

Adding a new Custom Logic column for Price Paid

Describe what you want — pull the linked Product's Price at the moment this order is created, and lock it in permanently, so it never changes even if the product's price is updated later.

Describing the logic to lock in the product price at the moment the order is created

Confirm the clarification screen, checking that it understood this value should freeze at creation, not stay live like a Lookup would.

Clarification screen confirming the price will be locked in at record creation rather than staying live

Save it. Priya's existing order, created back when the mug was $18, calculates its locked price using the value that was true at that time:

• Priya Nair's order → Price Paid: $18

Order record showing Price Paid correctly locked at 18 dollars

---

Step 4: Prove It Survives a Price Change

Now change the Ceramic Mug's price again, from $22 to $25.

Updating the Ceramic Mug's Price again from 22 to 25 dollars

Check Priya's September order one more time.

Priya's order's Price Paid stays at $18, completely unaffected, no matter how many times the product's price changes afterward.

Priya's order still correctly showing Price Paid at 18 dollars despite two subsequent price changes

Now place a brand new order for the Ceramic Mug today, at its current $25 price.

• New order → Price Paid: $25, calculated fresh at today's price

A new order showing Price Paid correctly locked at the current 25 dollar price

---

Recap

We solved two separate problems in this tutorial:

1. Duplication — a Customer table linked to Orders means a phone number lives in exactly one place, reused everywhere, instead of retyped and inevitably mistyped
2. Retroactive drift — a plain Lookup pulls a linked value live, which is correct for things like phone numbers, but wrong for anything that needs to reflect a specific moment in time
3. The fix — a Custom Logic snapshot field locks in the product's price at the exact moment an order is created, immune to any future price changes

This same combination — link to avoid duplication, snapshot to preserve history — applies well beyond purchase prices. Exchange rates, applied discounts, tax rates, or any value that needs to reflect "what was true back then" rather than "what's true now" all follow this exact same pattern.