Auto Number Columns Based on Other Column Input

JeffCMCO

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 

 

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. 

Jeff

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