Sequencial numbering based on Criteria
Can you please help with the following formula to work in Smartsheet:
=TEXT(VLOOKUP(C3,LIST!$A$2:$B$12,2,0),VLOOKUP(C3,LIST!$A$2:$B$12,2,0)&"0")&TEXT(VLOOKUP(D3,LIST!$D$2:$E$17,2,0),"00")&TEXT(COUNTIF($C$1:C2,C3)+1,"000")
I am creating a numbering system that are criteria based but then want them to be sequential based on the selected criteria:
See Attached.
The last 3 digit should be sequential based on the initial 3 digits which are criteria based (Department & Type). I am able to establish the first part, but are having difficulty getting the sheet to generate the 000 part.
The formula above works great in excel, not to get this to work in smartsheet is the problem.
Best Answer

You are going to need an AutoNumber column (called "Auto" in this example) that has no special formatting.
Then you would use this formula to generate your Doc #:
=formula_to_generate_first_portion + "" + RIGHT("00" + COUNTIFS(Department:Department, @cell = Department@row, [Document Type]:[Document Type], @cell = [Document Type]@row, Auto:Auto, @cell<= Auto@row), 3)
Answers

You are going to need an AutoNumber column (called "Auto" in this example) that has no special formatting.
Then you would use this formula to generate your Doc #:
=formula_to_generate_first_portion + "" + RIGHT("00" + COUNTIFS(Department:Department, @cell = Department@row, [Document Type]:[Document Type], @cell = [Document Type]@row, Auto:Auto, @cell<= Auto@row), 3)

Paul, Thanks  this worked great.

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!