New to Smartsheets Formula for Output Referenced on a Different Sheet

NCNWIncData

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

Screenshot 2024-02-06 125231.png

(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

DKazatsky2

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

Good Evening Dave,

This worked! YEAH A million smartsheet stars for you!

Have a wonderful weekend,

Jade

DKazatsky2

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

Good Evening Dave,

This worked! YEAH A million smartsheet stars for you!

Have a wonderful weekend,

Jade

NCNWIncData

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