The problem
A lot of Smartsheet forms rotate reviewers, approvers or case handlers across a team automatically. Usually that means one of two things: a COUNT-based formula that assigns each new submission to whoever currently has the fewest rows, keeping workload even over time, or a strict MODULO formula that cycles through the reviewer list in fixed order (1, 2, 3, 1, 2, 3) regardless of workload. Either way, the formula typically finishes with an INDEX/MATCH that looks up the actual reviewer name from a small reference table once it's worked out whose turn it is. That works well the first time someone submits the form.
The trouble starts on the second submission. If Priya's first request went to James for review, and Priya comes back with a revised version, a correction or a follow up form, a pure rotation formula has no memory of that. It just looks at how many rows exist so far and hands the new row to whoever is next in the rotation, usually someone other than James.
For a lot of workflows that's a problem, not a nuisance. Think of:
- A teacher grading resubmitted coursework, where continuity of feedback matters
- A compliance reviewer following up on a corrected document, who already knows the history
- A client account manager reviewing a revised proposal from the same customer
- Any approval chain where "the same person owns this case" is part of the process
The underlying requirement is always the same: assign new submitters at random or in rotation, but once a submitter (or a case, order or ticket) has been assigned, every future resubmission from that same source needs to stick with that original reviewer.
The building blocks
1. Give every original submission a stable, unique ID
Before anything else works, you need a way to recognise "this is the same case coming back" versus "this is a new one." An auto number column on the sheet gives you a unique row ID for every original submission.
2. Assign new submissions using your normal rotation logic
This part doesn't change. Whatever formula currently spreads new rows across your reviewer list, whether that's a running COUNTIF against each reviewer's name, an INDEX/MATCH against a rotation table, or a modulo based "next in line" calculation, keeps doing exactly that job, but only for genuinely new submitters.
3. Add an optional field to the form for the original reference
The auto number in step 1 only exists once the row has landed in Smartsheet, so it needs to make its way back to the submitter, typically through a confirmation email, a summary screen after they submit, or a lookup value they already hold, like an order number or employee ID. Add a second field to the form itself, something like "Original reference," and leave it optional. Someone submitting for the first time leaves it blank. Someone resubmitting fills it in with the reference they were given.
On the sheet, that field becomes the key your reviewer formula checks against. If it's populated, the row is a resubmission and the reviewer should match whoever handled the original. If it's blank, the row is new and falls through to your normal rotation logic:
=IFERROR(INDEX([Reviewer]:[Reviewer], MATCH([Original Reference]@row, [Reference ID]:[Reference ID], 0)), <your rotation formula>)
In plain terms: if the submitter gave a reference, look it up and reuse the reviewer already sitting on that original row. If they didn't, or the reference doesn't match anything, treat it as new and hand it to the rotation formula instead.
4. Turn that field into a dropdown, fed by a helper sheet
Leaving "Original reference" as free text invites typos, which means a resubmission that should match James's original case ends up with a broken lookup and gets reassigned at random instead, exactly the outcome you're trying to avoid. The fix is to make it a dropdown populated from a list of valid, existing reference IDs, rather than something people type by hand.
To build that list, set up a helper sheet and a Copy Row automation that fires whenever a new row is created on the main sheet. There's no need to trim this sheet down to a handful of columns. A Copy Row automation brings across the full row regardless, columns and all, so the helper sheet ends up as a complete mirror of the main sheet at the point each row was created, which also happens to make it a useful audit trail on its own.
Add a new column "Unique ID for dropdown" with a column formula =[Unique ID]@row
This is necessary for the next step as this feature cannot link with the auto-number system column.
5. Link the dropdown column to the helper sheet's unique ID column
With the helper sheet in place, set the "Original reference" column's type to dropdown, then toggle on Link to another sheet in its column properties. Point the source sheet at your helper sheet, and select the "Unique ID for dropdown" column from step 4 as the source, since that's the unique ID you're actually matching against in the lookup formula. Linking it this way turns on Limit to list values only automatically, so the dropdown pulls its options straight from the helper sheet's ID column instead of anyone having to keep a separate values list in sync by hand. As new rows land on the main sheet and get copied across, their reference IDs appear in the dropdown on their own.
A few refinements worth adding
- Label the field clearly. Something like "Only fill this in if you're resubmitting a previous request" on the form itself goes a long way toward keeping genuinely new submitters from populating it by accident.
- Archive the helper sheet in step with the main one. Since the helper sheet grows every time the main sheet does, it's worth adding an archiving automation to it as well, following whatever archiving method the main sheet already uses, whether that's moving rows out once a case is marked closed, or after a set number of days. That keeps the dropdown list limited to references that are actually still relevant, rather than every case the process has ever handled.
- Flag first submissions versus resubmissions. A simple checkbox or dropdown driven by whether the reference field is populated lets reviewers and dashboards see at a glance whether they're looking at a fresh case or a returning one, which is often useful reporting on its own.
- Consider whether the submitter should ever see or choose the reviewer. Some workflows are better served by keeping the whole assignment invisible to the person filling in the form, others benefit from showing a read only "assigned to" field once it's known, so they can reference it in follow up conversations.
The general pattern
Strip away the Smartsheet specifics and the pattern applies to any tool with forms, rotation logic and a database behind it: give repeat submitters an explicit, validated way to identify the case they're following up on, rather than trying to infer it purely from the data, and keep a running record you can look that reference up against. A helper sheet that mirrors the main one, with its own archiving in step with it, is usually a more reliable foundation for that than trying to make one formula do all the work in place.
If you're wrestling with a similar assignment, rotation or approval routing problem in Smartsheet and want a second pair of eyes on the sheet architecture, get in touch and we can work through it together.
