New to Smartsheets Formula for Output Referenced on a Different Sheet

I am creating essentially a unique ID for each user who completes the form.

I am trying to output a Unique ID in a column which needs

(1) Auto Number (I figured that out) and (2) concatenate with another row that has various specific prefixes on the same worksheet depending on the Organization filling out the form.

For example if Continental Societies, Inc. fills out the form I want create a Unique ID by combining the Abbreviated Org Name with the Row ID.

I'm just not sure how and which formula to use either INDEX or IF statement to look through the list of Full Org Names to select the correct Abbreviated Org. Name.


Thanks for the support,

Jade

Best Answers

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @NCNWIncData,

    Give this a try.

    =IF([Select your Affiliated Organization]@row <> "", INDEX([Abbreviated Org Name]:[Abbreviated Org Name], MATCH([Select your Affiliated Organization]@row, [Full Org. Names]:[Full Org. Names], 0)) + [Row ID]@row, "")

    Hope this helps,

    Dave

  • NCNWIncData
    Answer ✓

    Good Evening Dave,

    This worked! YEAH A million smartsheet stars for you!

    Have a wonderful weekend,

    Jade

  • Good Evening Dave,

    One more question, I had to move my reference data to a separate sheet from my survey results due to the fact it was throwing off my counter row (see image on the right).

    How would I reference a different worksheet with the same formula you provided? The name of the Worksheet that is referencing the data 'Reference Unique ID Form' (see on the left) have the two columns I want to Index/Match data which have the same titled as previously mentioned my post before Column 1'Full Org. Names' and Column 2 'Abbreviated Org Name'.

    Thanks for the support,

    Jade

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!