Auto number

I'd like a document numbering intake form that assigns an auto number based on a series of selections. How best to do that within the form/auto column?

Best Answer

  • Bill in Ohio
    Bill in Ohio ✭✭✭✭✭
    Answer ✓

    I have created a couple of these, but had to think a little differently and use a "helper" column that collects the information from the other columns. I do have an actual auto number column as part of the formula to ensure that each id is then unique, but a way to look at it can be like this:

    [Column A] {dropdown value}

    [Column B] {Autonumber - this can be fairly simple, or I typically will start with 1, and format as 0000. This gives your first record 0001}

    [Column C] {another dropdown value}

    Finally, the helper column, [Column D] is a formula = [Column A] + "-" + [Column B] + "-" + [Column C]

    Unfortunately you end up with something in Column D, reading down that looks like:

    • Sales-0001-NW
    • Servicing-0002-SE
    • Finance-0003-CT
    • Sales-0004-SE ….

    I am not sure if that will work for you, but that does give an easy method to create a unique auto-num field that can be referenced. If you need to make it "permanent," you will need to do some automation to store the initial selection in a different field, otherwise your unique field will change say when someone changes Sales to Servicing in the first row, but you can use "When Rows are Added" to store the very first entry and not update the custom auto-num when the values of the drop-downs change later.

    "Even my contingencies have contingencies."

Answers

  • Bill in Ohio
    Bill in Ohio ✭✭✭✭✭
    Answer ✓

    I have created a couple of these, but had to think a little differently and use a "helper" column that collects the information from the other columns. I do have an actual auto number column as part of the formula to ensure that each id is then unique, but a way to look at it can be like this:

    [Column A] {dropdown value}

    [Column B] {Autonumber - this can be fairly simple, or I typically will start with 1, and format as 0000. This gives your first record 0001}

    [Column C] {another dropdown value}

    Finally, the helper column, [Column D] is a formula = [Column A] + "-" + [Column B] + "-" + [Column C]

    Unfortunately you end up with something in Column D, reading down that looks like:

    • Sales-0001-NW
    • Servicing-0002-SE
    • Finance-0003-CT
    • Sales-0004-SE ….

    I am not sure if that will work for you, but that does give an easy method to create a unique auto-num field that can be referenced. If you need to make it "permanent," you will need to do some automation to store the initial selection in a different field, otherwise your unique field will change say when someone changes Sales to Servicing in the first row, but you can use "When Rows are Added" to store the very first entry and not update the custom auto-num when the values of the drop-downs change later.

    "Even my contingencies have contingencies."

  • Very helpful! Thank you!

  • Bill in Ohio
    Bill in Ohio ✭✭✭✭✭

    I'm glad that I was able to help someone! Now if I can answer about 200 more, I can balance my score between how many I answer, and how any people have helped me from their questions!

    "Even my contingencies have contingencies."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!