Auto-numbering with formula

Anoop Phago

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.

Paul Newcome

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)