Create Sequential, Unique ID for certain rows as column formula

Julie McAlee

I need a column formula that can add sequential, unique IDs to data like this coming in from a form:

Task Type Task ID

Manual M001

Automated

Manual M002

Manual M003

Manual M004

Automated

Manual M005

Task IDs that aren’t the manual type need sequential numbering within their type (i.e. A001, A002, etc).

Here’s the kicker: It has to be done with a column formula. No “just copy this formula down the row,” because that will break eventually without manual intervention, and business needs mean any solution will go unmaintained for long periods of time. I need to future-proof it as rows are added for years to come.

My initial solution to this was using Auto Number to give me consecutive numbers and creating an automation to move the Manual task type rows to a Manual Task. But I discovered the row number exits the sheet with the row, making the remaining row numbers skip.

I saw the solution below , but it requires entering a row number in its formula, which makes it ineligible to be a column formula.

https://community.smartsheet.com/discussion/40961/formula-to-create-unique-id-on-specific-rows

I appreciate any ideas on how to approach this.

Find more posts tagged with

Accepted answers

Paul Newcome

If you insert an auto-number type column with no special formatting (called "Auto" in this example), you would use something along the lines of

=IF([Task Type]@row = "Auto", "A" + RIGHT("000" + COUNTIFS(Auto:Auto, @cell <= Auto@row, [Task Type]:[Task Type], @cell = "Auto", 3))

All comments

Paul Newcome

Are you able to provide screenshots for context?

Julie McAlee

Of course! I didn't realize how bad my attempt to depict it in text would go:

That's my starting place, with annotation of how I want the column formula to be able to fill in the gaps not provided by the form entry. I'm sure it will require helper fields, which is what led me to try to shuffle the Manual task type rows to another sheet so I could use Auto Number.

I don't have automation results on hand for this example to screenshot, but this would leave me with an Auto Number column with 1002 and 1006 after the Manual rows were removed.

Paul Newcome

If you insert an auto-number type column with no special formatting (called "Auto" in this example), you would use something along the lines of

=IF([Task Type]@row = "Auto", "A" + RIGHT("000" + COUNTIFS(Auto:Auto, @cell <= Auto@row, [Task Type]:[Task Type], @cell = "Auto", 3))

Julie McAlee

Holy cat, this is amazing. Thank you! I've spent days trying to work around this. You're a lifesaver.

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.