Formula fails after 1000 rows

Have this formula that works like a dream until I get to 1000 rows

It basically creates a sequential number like this DM-0998, DM-0999.

="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", One@row))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), ".")

Once we hit 1000 rows the formula results in this. DM-10011001

This formula throws an invalid operator error instead of DM-1001

="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", One@row, IF(One@row < 1000, "", One@row)))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), ".")

Find more posts tagged with

Accepted answers

All comments

Detrie Zacharias

This mostly works except on row 1000 instead of DM-1000, I get DM-10001000

="DM-" + IF(One@row < 10, "000", IF(One@row < 100, "00", IF(One@row < 1000, "0", IF(One@row > 1000, "", One@row))) + JOIN(COLLECT(One@row:Two@row, One@row:Two@row, ISNUMBER(@cell)), "."))

Sean Morgan

Hello @Stavros_McGillicuddy !

Rather than using a Formula to number your rows, you may instead wish to use an Auto Numbering Column. With this, you can set your Prefix and Suffix. See more on this here: https://help.smartsheet.com/articles/1108408-auto-numbering-rows

Please let me know if you have any questions!

Regards

Sean

Detrie Zacharias

Hi Sean.. Thanks for your input.

I stared with an Auto Number column but, I moved to a formula so that I could keep sub tasks grouped with their tasks

DM-0998

DM-0998.1

DM-0998.2

etc

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.