Smartsheet Custom Auto Increment Format
Channel: Smartsheet Guru
Date: 2021-06-09

My eBook: Smartsheet User Guide for Accelerated Learning 👉 https://amzn.to/3i7jVPH (paid link)
Get my 7 Smartsheet Tips👉 https://www.smartsheetguru.com/7-smartsheet-tips/
I apologize, the audio in this video is pretty embarrassing.  See what I use to record now: https://smartsheetguru.com/mic (paid link)

In this example a Auto-Increment ID is generated in the format AA001 AA999 then rolls to AB001, etc.  with a maximum value of ZZ999


Here are the formulas used:


Starting in the first row:
3 Digit Number:
=IF([Number Increment]@row = 0, 0, IF(MOD([Number Increment]@row, [Maximum 3 digit value]$1) = 0, [Maximum 3 digit value]$1, MOD([Number Increment]@row, [Maximum 3 digit value]$1)))


ID
=CHAR([First Letter]@row) + CHAR([Second Letter]@row) + IF(LEN([3 Digit Number]@row) = 1, "00" + [3 Digit Number]@row, IF(LEN([3 Digit Number]@row) = 2, "0" + [3 Digit Number]@row, IF(LEN([3 Digit Number]@row) = 3, [3 Digit Number]@row)))


Starting in the 2nd row:
First Letter:
=IF(AND([Second Letter]1 = 90, [3 Digit Number]1 = [Maximum 3 digit value]$1), [First Letter]1 + 1, [First Letter]1)


Second Letter:
=IF(AND([Second Letter]1 = 90, [3 Digit Number]1 = [Maximum 3 digit value]$1), 65, IF([3 Digit Number]1 = [Maximum 3 digit value]$1, [Second Letter]1 + 1, [Second Letter]1))


Number Increment:
=[Number Increment]1 + 1



http://www.smartsheetguru.com
#smartsheet #smartsheetcoaching #smartsheettutorial #howtousesmartsheet #coaching