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 DM0998, DM0999.
="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. DM10011001
This formula throws an invalid operator error instead of DM1001
="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)), ".")
Answers

This mostly works except on row 1000 instead of DM1000, I get DM10001000
="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)), "."))

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/1108408autonumberingrows
Please let me know if you have any questions!
Regards
Sean

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
DM0998
DM0998.1
DM0998.2
etc
Help Article Resources
Categories
Check out the Formula Handbook template!