Auto-numbering with formula

Options

I am reaching out to seek your assistance regarding auto-numbering.

Here's the situation: I have a smartsheet file where one of the columns is the location, i.e., Nepal (NP), Pakistan (PK), and India (IN). And the second one is auto-numbering (where I need a formula) Now in the auto-numbering column, I want a formula so that the auto-numbering is based on the location as the rows are added, like for Nepal, the auto-numbering should go like NP-01, NP-02, NP-03, and so on.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would need an auto-number column (called "Auto" in this example) that has no special formatting in it.

    Then you would use this formula:

    =[Location Shortcut]@row + "-" + IF(COUNTIFS([Location Shortcut]:[Location Shortcut], @cell = [Location Shortcut]@row, Auto:Auto, @cell<= Auto@row)< 10, "0", "") + COUNTIFS([Location Shortcut]:[Location Shortcut], @cell = [Location Shortcut]@row, Auto:Auto, @cell<= Auto@row)