Sequencial numbering based on Criteria

KempenUSA

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.

Paul Newcome

You are going to need an Auto-Number 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 Newcome

You are going to need an Auto-Number 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)

KempenUSA

Paul, Thanks - this worked great.

Paul Newcome

Happy to help. 👍️