Auto Generating a Sequential Document Number Based on Cell Criteria
What formula can I use to autogenerate 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: PBIACTFRM0001.
"CompanyDepartmentTypeSequential 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. PBIACTFRM0001 would generate and the next time we select those same criteria, the next number to generate would be PBIACTFRM0002, and so on. If we had PBIPMGUI selected, then the first number generated would be PBIPMGUI0001. The next time we choose PBIPMGUI, the number generated for that would be PBIPMGUI0002, and so on.
Answers

You would need to first insert an autonumber 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 4digit number at the end. See below. Thoughts? Anyway to make it so that it is sequential? 0001, 0002, 0003, etc.

Did you insert the autonumber column and then save? It is dependent on the autonumber 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!