Announcing Formula Support for Contact Columns

Kara Lumley · 09/24/19

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å · 09/24/19

Excellent!

Thanks, Kara!

Have a fantastic week!

MakeItHappen · 12/04/19

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 · 12/04/19

Vlookup with a cross-sheet reference would work great.

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

Tony Christman · 12/18/19

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å · 12/18/19

@Tony Christman You 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!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

George Goosen · 01/16/20

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 · 09/25/20

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 · 11/17/20

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å · 11/17/20

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 by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Graham Rimell · 02/08/21

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

=Name@row + " <" + Email@row + ">"

It produces a cell like this:

John Smith <John.Smith@email.com>

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 <Email>" format.

It's a potential workaround anyway.

Emma Stanford · 02/10/21

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(ID@row, {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 <Email>".

Genevieve P. · 02/10/21

Hi @Emma Stanford

Based on the behaviour you're describing, the first thing I would check is the type of 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 a Contact type of column as well: you mention a "name column" in your source 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 · 02/17/21

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

Source column:

Screen Shot 2021-02-17 at 3.05.08 PM.png

Destination column:

Screen Shot 2021-02-17 at 3.07.06 PM.png

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

Screen Shot 2021-02-17 at 3.08.31 PM.png

Destination column type:

Screen Shot 2021-02-17 at 3.07.12 PM.png

Thanks for any help you can provide.

Genevieve P. · 02/22/21

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 suggest reaching out to Smartsheet Support with this information and screen captures so that they can troubleshoot with you in a private channel.

Announcing Formula Support for Contact Columns

Comments

Categories