Announcing Formula Support for Contact Columns

124

Comments

  • 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å
    Andrée Starå ✭✭✭✭✭✭

    Excellent!

    Thanks, Kara!

    Have a fantastic week!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • MakeItHappen
    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
    Mike Wilday ✭✭✭✭✭✭

    Vlookup with a cross-sheet reference would work great. 

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

  • 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å
    Andrée Starå ✭✭✭✭✭✭

    @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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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


    It's a potential workaround anyway.

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • 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.
    Genevieve P. Employee Admin

    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.