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)), ".")
Answers
-
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)), "."))
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!