Announcing Formula Support for Contact Columns

Kara Lumley

Maxim (and any others experiencing error messages with formulas in contact columns):

Please log out of Smartsheet and log back in. All of the #nomatch and #contactcolumnsdonotsupportformula errors should be resolved. Please let me know if not.

Thank you,

Kara

Andrée Starå

Excellent!

谢谢,卡拉!

Have a fantastic week!

MakeItHappen

I am using INDEX / MATCH to populate a cells in a CONTACT LIST column from a CONTACT LIST column in another sheet. However it is returning the individuals first and last name as TEXT and not making it a Contact.

How can I make this return a contact?

Reading the posts it seem like I need to create a Contact List sheet and then vlookup the email address.

Or am I missing something?

Mike Wilday

Vlookup with a cross-sheet reference would work great.

https://help.smartsheet.com/videos/advanced-cross-sheet-formulas

Tony Christman

What am I missing here? I'm putting a straight forward if statement into the "assigned to" column. Which looks like this:

=IF(Clad323 = 1, "Tony Christman", "")

Instead of putting the contact in, it just puts the text into the cell.

The top cell is from my formula and the lower is from picking from the contact.

How do I get it to put the contact and not text?

Andrée Starå

@Tony ChristmanYou have to either use an email instead in the formula or lookup the name in a table that gets the email address when the name is matched.

Make sense?

I hope that helps!

有一个很好的一天!

Best,

Andrée Starå

Workflow Consultant / CEO @WORK BOLD

George Goosen

I have been testing this with some success.

My requirement though is to concatenate the contacts from multiple "multi contact" columns into a single multi column.

Is it possible to concatenate 2 or more contact columns into a single larger multi contact column?

The intention is that, there are a number of business rules that drive who the next action owner is for a certain item on a sheet. I am trying to build a dynamic view that shows each action for the responsible parties?

Mark Candela

So I am not having an issue with my Index Match from a separate lookup however an issue with the contact email I'm trying to bring in not be notified. I have a index match setup to bring the manager in based upon the created by to send a notification to the manager. The created by notification works great, however the manager is not going through. The column is setup as contact not text.

If I remove the index match and enter the email it works great - its only the lookup that doesn't work.

Any suggestions?

Thanks, Mark

Katie G

Is it possible to use JOIN(COLLECT()) with contacts to fill in a multi-contact cell? I cannot figure out the syntax to make this work.

Andrée Starå

Hi@Katie G

Unfortunately, as far as I know, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå| Workflow Consultant / CEO @WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Graham Rimell

To chime in on this, We've had some success using the Formula:

[email protected]+ " <" +[email protected]+ ">"

It produces a cell like this:

John Smith <[email protected]>

If you then changed the cell to a contact column it seems to recognise it. However via a Vlookup it's not always 100% reliable and will very occasionally switch back to the "Name " format.

It's a potential workaround anyway.

Emma Stanford

Not sure what I'm missing here, but I'm still not able to get my contact cells formatted as contact cells when I use a VLOOKUP (or a simple = formula) from another sheet.

=Name1 formats correctly as contact.

=VLOOKUP([email protected], {Range on other sheet containing ID and Name columns}, 2) formats as plain text.

={Range on other sheet containing a single contact} also formats as plain text.

I've tried using a VLOOKUP from a list of email addresses rather than names, and that doesn't work either. Also tried Graham's tip above, which just displayed values as "Name ".

Genevieve P.

Hi@Emma Stanford

Based on the behaviour you're describing, the first thing I would check is thetypeof column in both sheets. If the source column is a Text/Number column then the values will pull through as text (including if that text is an email address, like in your last example).

The source column (you mention a "name" column) will need to be aContact type of column:你提到一个“名字列”在你的源头e sheet.

Let me know if this was the issue! If not, it would be helpful to see screen captures of both sheets, identifying the columns in the range and the column types, but please block out any sensitive data.

Cheers,

Genevieve

Emma Stanford

Hi@Genevieve P, Thanks for your response! That was the first thing I checked. Screenshots attached.

Source column:

Destination column:

Source range for "DFI Workflow - All collections Range 1":

Destination column type:

Thanks for any help you can provide.

Genevieve P.

Hi@Emma Stanford

Thank you for checking the columns! I'm unable to replicate what you're seeing here (my formula returns the contact) and I can't tell what may be happening from these images. I would suggestreaching out to Smartsheet Supportwith this information and screen captures so that they can troubleshoot with you in a private channel.