User Email in Column instead of Name

Options
ker9
ker9 ✭✭✭✭✭✭

Is it possible to get the Smartsheet user email in a column in addition to or instead of the user name? I'd like to be able to do a lookup on the email address. The email address is relatively consistent among various applications and names are not consistent. (I did search, but since the changes to help and community were made, it seems impossible to find anything.)

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/16/20
    Options

    Hi @ker9 ,

    I'm curious whether someone knows of a way to extract the email address from a contact cell. I don't. I do have a workaround.

    I use a helper sheet and vlookup. The helper sheet contains names and emails. I use the contact list cell in the main sheet to lookup the name in the helper sheet and return the email. This is easy if you only have a few contacts.

    If you have lots of contacts or you're collecting input from people outside your circle then there is a more complicated workaround. If you move the row to the helper sheet it brings the email address with it. The column property for the contact in the helper sheet needs to be Text/Number, not Contact.

    In the helper sheet, this formula will strip out the email from the text string. =MID([insert column name with contact]@row, FIND("<", [insert column name with contact]@row) + 1, LEN([insert column name with contact]@row) - FIND("<", [insert column name with contact]@row) - 1). I put this formula in a column [email] which can be a text/number or contact.

    Then, use vlookup in your main sheet to return the email from your helper sheet.

    Good luck. Hopefully someone has an easier answer and we can both learn.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    Hi @Mark Cronk - thanks for your answer. I also have a look up sheet but we are a large organization and people are coming and going all the time so maintaining it is onerous.

    I appreciate your response but I think I will leave this open in case someone else has another idea. :)

    It would also be nice if you could export the User List directly to a sheet or overwrite an existing sheet with the data.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Ker9,

    I'll be following this for a better solution. Lots of smart users out there.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!