How do I design an inventory sheet that preserves a record when an item changes location?
Quick answer
Keep two related tables: an item list with one row per item and its current location, and a movement log with one row for every move. Give each item a stable, unique ID that does not depend on its location or name, and record that ID on every movement row. Update the current location when an item moves, but add a new log row rather than replacing the previous movement record.
The question, the details, the next step.
I have a spreadsheet with an item name and location. When something moves, I replace the old location with the new one. What should I change so I can still see where it used to be?
Separate the quick snapshot from the history. Keep an Items sheet with one row per item and its current location. Add a Movements sheet where each move gets a new row. When an item moves, update its current location in Items and append a movement record; do not overwrite an earlier record.
This gives you two useful answers: “Where is it now?” comes from Items, while “Where has it been recorded moving?” comes from Movements. The movement log is only as complete as the records people enter, so it should not be treated as proof that every move was captured.
How do I make sure a movement is attached to the right item, especially if two items have similar names?
Assign each item a stable ID, such as ITM-0042, and use that ID in both sheets. A name or location can change; the ID should not. If you already use a readable name, keep it as a separate description, not as the only link between records. Avoid reusing an ID for a different item.
For example, the item row might be:
ItemID | Description | CurrentLocation
ITM-0042 | Blue storage case | Shelf B
A movement row might be:
MoveID | ItemID | From | To | Date | RecordedBy | Note
MV-0108 | ITM-0042 | Shelf A | Shelf B | 2025-03-08 | [name or initials] | Relabeled
The date and recorder fields are practical choices, not a guarantee of who physically moved the item. Use a consistent date format and a clear convention for the optional note.
What should I do when the old location is unknown, or the item is new and has never been moved?
For a new item, create its Items row with its starting location. You can either record that starting placement as the first movement-like event or keep it out of Movements and label the field clearly—for example, use an event type such as Initial placement. Pick one approach and apply it consistently, so readers can tell what the log includes.
If you do not know the previous location, do not guess. Leave the From field blank or enter a consistent value such as Unknown, and use a note to explain the gap if useful. If the item’s current location is also uncertain, say so in the Items sheet rather than presenting an unverified location as certain.
Can you show me a simple routine for recording a move without making the two sheets disagree?
Use this order each time: identify the item by ID; add a new movement row with the old and new locations and the date; then update that item’s current location in Items. If you discover a mistake later, preserve the original row and add a correcting record or clearly mark the correction according to your chosen convention. Do not quietly erase history if the purpose of the sheet is to retain it.
For a small spreadsheet, a person can check that the newest movement’s To value matches the item’s CurrentLocation. With many rows, filters or a lookup can help find an item’s entries, but the sheet layout alone does not automatically ensure entries are complete or correct. Start with this copyable structure:
Items: ItemID | Description | CurrentLocation | Status
Movements: MoveID | ItemID | Date | From | To | EventType | Note
Keep IDs unique, make the ItemID spelling identical in both sheets, and decide whether initial placement and corrections belong in the log. A short written rule beside the sheet can prevent different people from recording the same event in different ways.
A practical checklist
Create one Items row per item and assign a stable, unique ItemID.
Create a separate Movements sheet with one new row for each recorded move.
Include ItemID, date, From, and To on movement rows; add a note or recorder field if useful.
Choose a consistent way to record initial placement, unknown locations, and later corrections.
After each move, check that the Items current location agrees with the latest recorded destination.
A few more useful answers
Should I store the item name in the movement log as well as its ID?
You can include a description to make the log easier to read, but use the stable ItemID to connect records. Names may change or be similar across items.
Should I delete a movement row if someone entered the wrong location?
If you need a history, avoid silently deleting it. Use a consistent correction method, such as adding a clearly labeled correction row or marking the original as corrected, while keeping the current location accurate.
Does this sheet prove an item was physically at every recorded location?
No. It preserves the entries people make; it cannot establish that every move was recorded or that an entry reflects a verified physical check.