Copying a cell with an auto number from one sheet to another overwrites existing auto numbers
Hello, I'm using two intake sheets for different project types. Each intake sheet is populated by an idea submission form and the entries receive a unique number with an Auto Number column. So far so good.
Situations come up where a project of type A needs to be moved to type B. We do this with a checkbox, which when checked triggers an automation that moves the project row from sheet A to sheet B with the Move To Another Sheet function.
The trouble is that the moved project retains its auto number from the previous sheet. This results in projects with duplicate numbers if the number already existed on the target sheet.
Even if we rename the Auto Number column in sheet A to something different from the Auto Number column in sheet B, the move row function still places the previous number in the Auto Number column on sheet B.
Should we in this situation not use Auto Numbers? In that case, I don't know how we would give each project a unique number. Putting in a new column with an automatically incrementing number (e.g. previous cell number + 1) would not work because if a row is deleted or moved, all numbers after that row would change.
Has anyone encountered this issue? Thank you.
Comments
-
I would suggest adding some sort of prefix that is unique to each sheet.
-
We add a prefix automatically based on the Auto ID and the type of project. However, if project A-121 is transferred to sheet B, it becomes B-121. I can add a conditional based on if it is a transferred project, e.g. B-121X. But that is an ugly solution and there could still be confusion with the existing project B-121.
Is there a way to trigger the automatic numbering on a sheet?
