Formula to change a name to their numeric ID

Soni Khanna

Can I get assistance in writing a formula that will look at who created the row and instead of showing their name I want it to show their numeric ID i assign to them?

Paul Newcome

Start typing the formula:

=INDEX(

Then in the helper box there should be a link to "Reference Another Sheet". Click on that link, select the sheet you want to reference, then click on the column header that you want to reference. Changing the reference title is up to you. I generally change it to sheet name and column name so it is easier for me to follow.

Once you click on the blue button in the bottom right corner of the popup window, it should take you back to the sheet you were working on and have the cross sheet reference inserted. Just finish out the formula using the above steps to create the other cross sheet reference and you should be good to go.

Paul Newcome

Do you have some sort of lookup sheet that has the names in one column and the IDs in another column?

Soni Khanna

I can create one. Then what do I do from there? I'm still getting used to Smartsheet functionalities. LOL

Paul Newcome

You will need to use an INDEX/MATCH to pull the ID over based on the name. If you go ahead and create the sheet and share screenshots of both sheets (at least the column headers), I would be happy to help with a more exact formula.

Soni Khanna

Great thank you! Below I would like the QAS Team Member (name) to change to Auditor # :

Paul Newcome

Is that the sheet we are putting the formula on or the lookup table? If it is the sheet we are putting the formula on, I am going to assume the Lookup Table will use the same column names.

=INDEX({Lookup Table Auditor #}, MATCH([QAS Team Member]@row, {Lookup Table QAS Team Member}, 0))

Soni Khanna

Thank you! I created a separate sheet that I would need to link the formula to. How would I do that?

Paul Newcome

Start typing the formula:

=INDEX(

Then in the helper box there should be a link to "Reference Another Sheet". Click on that link, select the sheet you want to reference, then click on the column header that you want to reference. Changing the reference title is up to you. I generally change it to sheet name and column name so it is easier for me to follow.

Once you click on the blue button in the bottom right corner of the popup window, it should take you back to the sheet you were working on and have the cross sheet reference inserted. Just finish out the formula using the above steps to create the other cross sheet reference and you should be good to go.

Soni Khanna

Paul, thank you SO much for your help, it worked :)

Paul Newcome

Happy to help. 👍️