How to record and lock a Value in Smartsheet Without Any Premium Apps

Every Smartsheet user eventually hits the same wall: they need to record a value once, such as a due date, a committed price, or a baseline milestone, and then stop anyone from changing it. Smartsheet's native "Lock Column" feature looks like the obvious answer, but it doesn't actually solve this problem. It's worth understanding why before looking at two workarounds that do.

Why native column locking doesn't do what you'd expect

Locking a column stops users with Editor permissions from touching the cells in it. That's genuinely useful, but it has two gaps that matter here:

• Admins and Owners can still edit locked columns. Locking a column only restricts Editors. Anyone with Admin or Owner access on the sheet can edit it regardless. If the person you're trying to protect the value from happens to be an Admin, the lock does nothing for them.
• It doesn't distinguish "first entry" from "later change." A locked column can't be edited by Editors at all, including the very first time. That's fine for a field you fill in once as the sheet owner, but it doesn't help if the field needs to be editable initially and then frozen after a condition is met, which is exactly the "record it once, then protect it" pattern most people are actually after.

So a locked column protects a field from a category of user, not from a change happening at the wrong time. To get that second kind of protection, you need the value to be recorded somewhere the person editing the sheet can't reach, and you need automation to decide when that recording happens. That's what both methods below are built around.

The shared building blocks

Both approaches use the same three ingredients:

1. A Unique ID column on the source sheet (an Auto-Number column works well), so rows can be matched or reordered reliably across sheets.
2. A "lock" flag, either a single-option dropdown or a checkbox, that marks a row as no longer eligible for capture.
3. A second, empty copy of the sheet that acts as the place where the protected value actually lives, since anything stored there is out of reach of whoever is editing the source sheet day to day.

With those in place, the two methods differ in how the value gets from "just edited" to "safely recorded."

Method 1: Move the row, then move it back

This is the more mechanical of the two: the row itself travels between sheets.

Below we will assume that what you want to record is called "Value".

Setup

• Add the Unique ID column (optional here, mainly useful for reordering rows later).
• Add a dropdown column, "Lock", with a single option: "Locked" (a checkbox works just as well).
• Duplicate the sheet and empty the copy.
• In both the source and the copy sheet, add a "Value Locked" column, and lock it in both.
• In the copy sheet only, make that column a formula: =[Value]@row.

Automations

• Source sheet: when "Value" changes, and "Lock" is not "Locked", move the row to the copy sheet.
• Copy sheet: when a row is added, set "Lock" to "Locked".
• Copy sheet: when "Lock" changes to "Locked", move the row back to the source sheet.

So an edit to "Value" sends the row on a short round trip: it lands on the copy sheet, gets tagged "Locked", its own "Value Locked" formula captures the value that just arrived, and the row is sent straight back to the source. From that point on, the lock condition on the source automation is no longer met, so further edits to "Value" won't trigger another capture.

Pros

• Simple to reason about: there's no cross-sheet formula syntax involved, just move actions.
• The captured value lives in a genuinely separate column ("Value Locked") that's locked in both sheets, so it's not something an Editor can touch directly.
• Works cleanly with a checkbox if you'd rather not use a dropdown.

Cons

• The row physically leaves the source sheet, even if only for a moment. On a large or heavily automated sheet, that's two extra automation runs and two chances for something to go wrong mid-flight, such as a user editing the row while it's away.
• Anyone watching the sheet will see rows briefly vanish and reappear, which can be confusing without an explanation.
• Reports, dashboards or other automations that reference the source sheet by row position can be disrupted by rows leaving and returning, since Smartsheet appends moved rows at the bottom rather than restoring their original position.
• An admin can still edit the "Value locked" column by mistake if they're not careful.

Method 2: Copy the row and reference the copy sheet.

This version keeps every row where it is and uses formulas to pull the protected value back, rather than sending the row away to fetch it.

Setup

• Add the Unique ID column. Unlike Method 1, this one is mandatory, since it's the only thing the MATCH() formulas below use to find the right row.
• Add the dropdown (or checkbox) column, "Lock", with the single option "Locked".
• Duplicate the sheet and empty the copy.
• On the source sheet, add "Value Locked" and lock the column. Give it this column formula: =IFERROR(INDEX({Value column in copy sheet}, MATCH([Unique ID]@row, {Unique ID column in copy sheet}, 0)), "")
• Add a similar formula for the lock status: =IFERROR(INDEX({Lock column in copy sheet}, MATCH([Unique ID]@row, {Unique ID column in copy sheet}, 0)), "")

Automations

• Source sheet: when "Value" changes, and "Lock" is not "Locked", copy the row to the copy sheet.
• Copy sheet: when a row is added, set "Lock" to "Locked".

That's it, only two automations instead of three, because there's no return trip. The copy sheet becomes a one-way archive: every qualifying edit adds a new, locked snapshot row, and the source sheet's INDEX/MATCH formulas pull the matching value straight back for display, without the row ever leaving.

Pros

• Rows never leave the source sheet, so there's no disruption to row order, reports or dashboards built on it.
• Fewer automations to maintain (two rather than three), and no round-trip timing to worry about.
• The copy sheet naturally becomes an audit trail: every locked edit adds a row rather than overwriting one, so you can see the full history of captured values per Unique ID if you don't filter it down.
• The "Value Locked" column has a column formula, which makes it less likely to be edited by an admin by mistake, since it requires extra steps to edit it.

Cons

• More setup work up front: two INDEX/MATCH formulas per protected field, both of which depend on getting the cross-sheet references exactly right. If you're struggling with this type of formula, you can use the "Link Column Data" feature in the table view.
• Because the copy sheet accumulates a new row per capture rather than one row per item, the MATCH() formula will return whichever matching row it finds, generally the first one, so if you need "the most recent locked value" rather than "a locked value", you'll want to add a sort or filtering step, or periodically archive older matches out of the copy sheet. The solution could be to use INDEX/COLLECT instead.
• The Unique ID column is not optional here. Without it, the MATCH() formulas have nothing reliable to key on.

A technical detail that explains why both designs work

It's worth knowing about a Smartsheet automation restriction that shapes both methods, even though neither one runs into it: cells containing cross-sheet formulas or cell links don't trigger Move Row, Copy Row, Lock Row, Unlock Row or Approval Request automations. In other words, if you tried to trigger a move or copy off a change to "Value Locked" in Method 2 (the INDEX/MATCH column), it simply wouldn't fire, since that column is itself a cross-sheet formula.

Both methods sidestep this by triggering off "Value", which is a plain, directly-edited field on the source sheet, and only using the cross-sheet formula for display. If you ever extend either pattern, keep the trigger on a native field and let the formula stay downstream of it.

A couple of things to build into either version

• Locking the helper columns doesn't stop Admins. If the person you're protecting the value from has Admin or Owner access to the sheet, they can still edit locked columns directly. If that's a real risk, the actual protection comes from keeping the true value on a separate sheet they aren't shared into, not from the lock icon itself.
• Update requests can bypass column locks. Users completing a row via an Update Request form have been known to edit fields that are locked on the sheet itself, so if you're distributing this field through forms or update requests, test that path specifically rather than assuming the lock covers it.

Other native options worth knowing about

Before building either workaround, it's worth checking whether one of these simpler, fully native options actually covers what you need. None of them is a drop-in replacement for the two methods above, but each solves a nearby version of the problem with less setup.

Auto-lock the whole row, not just the field. Smartsheet's automation builder has a native "Lock Row" action: trigger it off a status change (for example, "when Status changes to Complete"), and the entire row becomes uneditable to Editors, with no companion sheet required. This is by far the simplest option if freezing the whole row is acceptable. The catch is that it locks everything, not just the one field you're trying to protect, so it doesn't work if other columns on that row need to stay editable after the value is captured. It's also worth noting Smartsheet doesn't offer an equivalent "Lock Column" automation action. Auto-locking is a row-level action only, which is part of why the two field-level methods above need a companion sheet in the first place.

Baselines, if the field is a project Start or Finish date. If your "Value" field is a project date, Smartsheet's built-in Baseline feature (Gantt view, toolbar, "Baselines") may already do this natively: it snapshots the Start and Finish dates into read-only "Baseline Start" and "Baseline Finish" columns and calculates the variance automatically as the live dates change. Only sheet Admins and Owners can set or reset a baseline, everyone else sees it as read-only. The limitation is scope: it only captures a Start/Finish pair on a Gantt-enabled sheet, one baseline per sheet, so it won't help with an arbitrary "value" field like a price or a status.

Cell History, if what you actually need is an audit trail rather than prevention. Right-clicking any cell and selecting "View cell history" shows every past value it's held, who changed it, and when, with no setup at all. It doesn't stop anyone editing the field, but if the underlying goal is accountability ("I need to know if and when this changed") rather than hard prevention, this native, zero-configuration feature may already answer the question the two workarounds are built to solve.

Which one should you use

Start with the three native options above: if locking the whole row is fine, use Lock Row automation and stop there. If the field is genuinely a project Start/Finish date, use Baselines instead of building anything. If you only need an audit trail rather than hard prevention, Cell History already gives you that for free.

If none of those fit, and you specifically need one field locked while the rest of the row stays editable, fall back to the two workarounds. Method 1 is the quicker build if you're comfortable with rows briefly changing sheets and don't have downstream reports that are sensitive to row order. Method 2 takes a little longer to set up correctly but is the sturdier choice for anything shared widely, reported on, or included in dashboards, since the source sheet's rows never move.

Neither replaces what Bridge or a proper audit-trail setup would give you, but both get you a genuinely protected, native-only "value once recorded, can't be casually changed" field, without needing a single premium app.

If you're trying to get this working in your own sheet and want a second pair of eyes on the formulas or automation logic, get in touch, this is exactly the kind of Smartsheet architecture problem I help clients untangle.