THE BRIEF
Our inventory spreadsheet shows #REF! errors everywhere, and the 'reorder' column says we need 400 inner tubes when we have plenty. Someone deleted a column last month. Can you fix it?
Scenario: Owner of an independent bike shop
Package: Fix a broken spreadsheet · $20 · 60 min
Here's the fixed sheet. Every #REF! is gone, the reorder column is right again, and I added a "How this works" tab. What changed:
- Restored the "On order" column that was deleted. That's what broke 212 formulas.
- Fixed the reorder formula, which was counting each sale twice.
- Locked the formula columns so they can't be deleted by accident.
EXAMPLE FIRST: INNER TUBES, 700x25
| Before | After | |
|---|---|---|
| In stock | 46 | 46 |
| On order | #REF! | 50 |
| Sold last 30 days | 88 | 44 |
| Reorder? | 400 | 0 |
- In stock
- Before
- 46
- After
- 46
- On order
- Before
- #REF!
- After
- 50
- Sold last 30 days
- Before
- 88
- After
- 44
- Reorder?
- Before
- 400
- After
- 0
You were right. You had plenty.
WHAT WAS WRONG
- The deleted column. Formulas pointing at it turned into #REF!. I rebuilt it from your last 4 supplier invoices.
- The "sold" count. It used a range that included the total row at the bottom, so every item counted its sales twice.
- The reorder math. It didn't subtract what was already on order.
THE NEW REORDER FORMULA
=MAX(0, MinStock - InStock - OnOrder)
In plain words: order enough to get back to your minimum, counting what's already coming, and never a negative number.
THE NEW SOLD FORMULA
=SUMIFS(Sales[Qty], Sales[SKU], A2, Sales[Date], ">="&TODAY()-30)
In plain words: add up how many of this item sold in the last 30 days.
THE "HOW THIS WORKS" TAB
- What every column means, one line each.
- Which columns you type in (white) and which ones calculate (gray, locked).
- How to add a new product without breaking anything: insert a row above the total line.
CHECKED
Compared 30 items against a hand count your staff did on Saturday. All 30 match.
— Marco
