Renumbering when row is moved via automation

Hello,

I have two sheets. One is "Current tasks" and one is "Completed tasks". I have the rows (individual tasks) auto number when entered (2026-001, 2026-002, etc.). When the row is marked complete, it is moved to the "Completed task" sheet.

The problem I am having is that when the row is moved the other tasks that remain in the Current Tasks sheet get renumbered (e.g., 2026-002 becomes 2026-001). Is there any way to assign the number when first entered, but then not allow it to be changed? We reference this number in other places, so it needs to remain the same.

Any ideas are appreciated!

Bill

Find more posts tagged with

Accepted answers

Paul Newcome

Usually in these cases, I will use an "intake sheet" that calculates the unique id and then use a copy row automation to push it to a "working sheet" as static data. The COPY row part is the key so that your intake sheet that outputs the unique identifier maintains all rows and doesn't end up shifting.

Assuming you are using a formula here which is why it changes when rows are moved off, I would also assume you are using a COUNTIFS in the formula that outputs the identifier. If that is the case, you can add another COUNTIFS that looks at the other sheet and gets the count from both. This should also keep the numbers from changing when rows are moved. I have also used this method a time or two with success.

There are also other formulas that can be used with helper columns, but generally speaking, I usually go with one of the above two options first.

All comments

Jason P

Would locking the column work?

Whalstead1

No, unfortunately it still changes the number when the column is locked.

Paul Newcome

There are also other formulas that can be used with helper columns, but generally speaking, I usually go with one of the above two options first.

Whalstead1

Thank you Paul. I think I will go with the helper sheet.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.