Formula fails after 1000 rows

Detrie Zacharias

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)), ".")

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