Auto Generating a Sequential Document Number Based on Cell Criteria
What formula can I use to auto-generate a sequential number into the Document Number column for each row based on the information placed in Company, Department, and Type? I need the numbers to look like this: PBI-ACT-FRM-0001.
"Company-Department-Type-Sequential Number"
We have several Department and Type options in a dropdown. So, the variations are large but we only want a number to be used once, i.e. PBI-ACT-FRM-0001 would generate and the next time we select those same criteria, the next number to generate would be PBI-ACT-FRM-0002, and so on. If we had PBI-PM-GUI selected, then the first number generated would be PBI-PM-GUI-0001. The next time we choose PBI-PM-GUI, the number generated for that would be PBI-PM-GUI-0002, and so on.
Answers
-
You would need to first insert an auto-number column with no special formatting (called "Auto" in this example).
Then you would use something along the lines of=Company@row + "-" + Department@row + "-" + Type@row + "-" + RIGHT("000" + COUNTIFS(Company:Company, @cell = Company@row, Department:Department, @cell = Department@row, Type:Type, @cell = Type@row, Auto:Auto, @cell <= Auto@row), 4)
-
So, I did the formula you suggested. It generates a document number but duplicates the actual 4-digit number at the end. See below. Thoughts? Anyway to make it so that it is sequential? 0001, 0002, 0003, etc.
-
Did you insert the auto-number column and then save? It is dependent on the auto-number column being populated, but that columns doesn't populate until the sheet is saved.
-
Thank you! I did not save the sheet. It is now generating sequential document numbers. Thank you, again!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!