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

JeffCMCO
JeffCMCO ✭
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? 

Thanks 

 

Tags:
· Share on FacebookShare on Twitter

Comments

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

     

     

    · Share on FacebookShare on Twitter
  • Ryan Robertson
    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.

     

    community.PNG

    · Share on FacebookShare on Twitter
  • JeffCMCO
    JeffCMCO ✭

    Ricki and Ryan, 

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

    Jeff

    · Share on FacebookShare on Twitter
  • 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.

    Craig

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories