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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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)

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!