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

11/29/17 Edited 12/09/19

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? 

Thanks 

 

Comments

  • rickiricki ✭✭✭✭✭

    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)

     

     

  • 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.

     

    community.PNG

  • Ricki and Ryan, 

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

    Jeff

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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.

    Craig

This discussion has been closed.