Generate an AutoNumber based on "Yes" being selected in a drop down

This formula seems to be in error - I get #UNPARSEABLE

=IF([MY FIELD NAME] = "Yes", AUTONUMBER(), "")

It would be even better if I could create the number when a checkmark is selected in another column.

EDIT: I am trying this for the checkbox:

=IF([MY FIELD NAME]@row, AUTO_NUMBER(), "")


My field format is Text/Number

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I did have an answer typed out that said you were missing the result in your expression and needed to add = 1 but then I tested it and it worked without. So I learned something new as well!

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Hello,

    You will likely need to add an additional column that has the properties of "Auto Number".


    With the Auto Number established the following formula will preform as it should:

    =IF([MY FIELD NAME]@row = 1, [Auto Number]@row, "")


  • Jeff Chamberlain
    edited 02/01/24

    OK - based on your feedback and some research, it looks like my problem is I can't have more than one auto number per sheet. That is a huge gap that Excel doesn't suffer from.

    So if I want to do a work around - I tried to append my current AutoNumber field with "-MRC". My expected behavior would be, check the box, then a value would show up in this new field. I created a new text/number field and entered the following:

    =IF([Requires MRC Number]@row, [Case Number]@row & "-MRC", "")

    I still get the #UNPARSEABLE message.

  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Jeff Chamberlain

    The symbol to join text or cell strings in smartsheet is + not &

    Try

    =IF([Requires MRC Number]@row, [Case Number]@row + "-MRC", "")

  • Thanks - it also appears as if I was not identifying that the box was checked with this:


    =IF([Requires MRC Number]@row = 1,

    I'm relearning formulas here and making some basic mistakes. thanks for your input.

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    I did have an answer typed out that said you were missing the result in your expression and needed to add = 1 but then I tested it and it worked without. So I learned something new as well!

  • Interesting - I might retry it.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!