Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Auto Number Columns Based on Other Column Input

edited 12/09/19 in Archived 2017 Posts

Is it possible to link the auto number format to data in other columns? 

For instance, if the user selects a particular product type in a form (Soda, Water, Juice), can I create an auto number column format that looks like Soda-001 or Juice-001? 




  • ricki
    ricki ✭✭✭✭✭✭

    you could add a column which would have the formula that uses the item id autonumber with the other data and then just hide the autonumber from view?

    =[Column2]14 + "-" +  ItemID14 (this was in row 14)



  • Ryan Robertson

    If one wanted independent auto-numbers for each type, one could also use a formula to count the number of rows with that type and create an identifier based on that.

    =Type1 + "-" + RIGHT(1000 + COUNTIF(Type$1:Type1, Type1), 3)

    Since I'm not using the auto-number ability to prefix zeros, I use the RIGHT() function to reproduce that functionality.

    The COUNT() function checks a range of the Type column for values that equal the type in that row.



  • JeffCMCO

    Ricki and Ryan, 

    Thanks a ton. I have both methods working. Problem solved. 


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Be aware that solutions like this will fail if the rows are sorted.

    Smartsheet does not have a 'toggle' or 'lock formula results' feature to prevent the formula from recalculating after the rows are sorted.


This discussion has been closed.