Extract text from one cell to populate the assignee cell in a Smartsheet recognizable contact?

Jay M

I have a challenge i have been racking my brain on for a while.

I want to extract names from my ACTION ITEM cell that will update the ASSIGNMENT correctly instead of just a text string. I am using the =MID, FIND ":", LENS, FIND ":" to extract & populate the HELPER COLUMN, but then want to make sure that the ASSIGNEE will update as a recognized Contact, so that reports, status request will work corrently.

Helper Column Formula:=MID([Action Item]@row, FIND(":", [Action Item]@row) + 1, LEN([Action Item]@row) - FIND(":", [Action Item]@row))

Assignee Column Formula:????Example below that Steve is in a text/number columm Helper column Extracts the name after finding ":"But when it is used to put into the Assingee is comes across as text instead of a contact that is on the page Like Elliot and Brijesh

In the above image, even though the formula updated the correct name, it did not enter it as a recognizable smartsheet contact. I manually updated Elliot and Brijesh for the Sheet to update to the recognized Contact instead of the string text.
As for the mutliple contacts, if there is a solution, that would be great but not a priority. This approch would be applicable to extract the Due date which is in a differnet column.

Adam Murphy

Unless you have an exceptionally large list of possible Assignees, the easiest solution I can think of is a helper sheet with 2 columns, their "short name"(what you are getting in the Helper Column) and the actual contact. Then just use an index/match to reference your helper sheet and return that actual contact for the Assignee field. For the multiple contacts, that's trickier…maybe a second helper column to split those into 2 names then lookup each and join them. I think it's possible. If you decide to go this path I can try to help further. Hope this helps.

Jay M

@Adam Murphy, I have all possible project contacts "loaded" on the sheet so it should be able to cross reference the Text String to the SS Contact… but to no avail. I have a Contact sheet as well with First, Last, Full name, Email and the SS Contact. Not quite sure how I could us that as an old V-lookup to replace with the recognizable SS Contact.

Adam Murphy

@Jay M Assuming the first name will always match and you don't have duplicate first names, this old school vlookup approach will work if you put the formula in the Assignee field you are trying to populate. I do this frequently for various reasons. I don't actually use vlookup (it requires that you never reorder your columns), I use index/match or index/collect, like this: =INDEX({Helper Sheet Contact}, MATCH([Helper Column]@row, {Helper Sheet FirstName}, 0))

Hope this helps!