Formulas in Contacts Columns

AFlint

I have researched this issue through multiple earlier discussions, but I can't find a permanent or reliable solution.

I am trying to use formulas to populate contacts through multiple stages of a workflow- currently 2 sheets- and cannot replicate the successful behavior documented in many other threads.

Sheet 1: Job Posting requisition. Rows in this sheet are created via a form that requires the user to be logged in. The user is then assigned to the "Submitted by" column, an Auto-Number/Created By column. In the very next column, I have a Contact column with the formula =[Submitted by]@row. My expectation is to get the standard contact field structure. Instead, I get an email address.

Expected:

What I get:

The only way to get to "Expected" is to click on the Hiring Manager cell and select the contact from the options.

Notes: The contact column is not restricted to a specific list (tried that- didn't change the outcome) and has Multiple unchecked

Sheet 2: Onboarding checklist. Once the requisition is filled with a candidate, I have a second sheet that uses a VLOOKUP via the Requisition ID to populate multiple columns, including Hiring Manager. Here, I get the same issue- I can return the email format, but can't get a contact with name. I even tried a helper column to just lookup the Submitted by- same behavior

Can anyone advise if my expectations are possible- to get a Contact in the standard contact format using any formula?

Thank you

Athar

AFlint try editing column properties and select "contact list" from given options. If that doesn't work, check your contacts and add names against email addresses. I hope it'll solve your problem.

AFlint

@Athar Sorry I didn't clarify this, but the contact columns are all formatted as Contact List in Column Type, and it has not worked in any variation. I've read multiple threads (seemingly this is an issue since the formulas in Contacts became available) but have not found any permanent solutions.

Athar

@AFlint I also have contact column with formulas in my sheet but I didn't get any such issues.

Please share screenshot with double click on contact cell from where contact is being called in Submitted by or Hiring Manager Cell. Thanks.

AFlint

Hi @Athar

I'm not sure I'm understanding what you are requesting. When I double-click, the formula becomes editable. Is this what you want to see?

Here is the column configuration for Hiring Manager above

Here is the configuration for Submitted by

Athar

Hi AFlint, thank you for sharing.

I have checked the issue. The column property of "Submitted by" i.e. Auto-Number/ System is the issue. One possible work around is to create a Master Contact Sheet with Contact Column (contact type) like standard contact format and Email address (text type). Then you can use Index/ Match function in Hiring Manager Column to bring the Contact in desired format.

You could use following formula.

=INDEX({Fetch Contact}, MATCH([Submitted by]@row, {Email Address}, 0))

Fetch Contact and Email Address are sheet references from Master Contact Sheet.

I hope it'll solve your problem.

AFlint

Thanks @Athar -

I had seen that solution in other threads on similar topics, but it would be too much work to maintain with the number of hiring managers we have throughout the company. I can only hope that SmartSheet is looking into this and working towards a more elegant solution.

Thank you for your time and effort.

Athar

It will be one time activity and your company's IT department can create such excel file that you can import into Smartsheet. Maintaining new contacts will be a routine activity no doubt.

No problem. You're welcome @AFlint