Importing from Excel to Smartsheet (Grid) using names for contacts

I am trying to import an excel spreadsheet where I have names in a column on each row. The names are formatted as FirstName<sp>LastName, and they are being imported then the cells are being referenced in other SS grids. I would like them to come in (during the import) as SS contacts (name, icon, email shown and can be used with automation, etc). I searched this and tried a number of things and determined (not sure) that I don't think that is possible. I can do this if I have the email but it doesn't really give me the SS contact that I want. SO, I tried using FirstName<sp>Lastname , <name@email.com> as the value, that did not work, and I tried to build that after the import and reference the cell to a cell in a col. that was contact list. I also tried to use the Name pulled into the import and reference the cell from another SS grid into a col. for contact list, that did not work. I then tried to build a lookup table with one column as Name (first last) then a col next to it with the SS contact for that name. Then in a SS grid, I used the Vlookup with no luck and then tried Index/match which did something interesting. It seemed to work but was inconsistent. After some research, it appears to be working for only those users in SS that are admins. Very difficult to figure out but I need some way to work with a name in a xl ss, and get it to a SS grid where the SS contact shows. I don't think I will have email available unless I use a lookup but using email does not give me the same SS contact that I would get if I simply started typing the name and chose from the lookup. Any help would be apricated.

Thank you,

Kevin

Happy to help if I can.

☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Best Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Look up Table

    VLookUp Grid


    Please let me know if these come through.

    Also attaching as files.


    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Answers

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭

    I have attached the grid I am using as the look up table. Named: "Name Lookup"

    Also attached is the grid I am using to perform the vlookup.

    This is the formula I am using that works only for the Admins (Kevin, Puru, and Bryan)

    =INDEX({Name Lookup Range 2}, MATCH(Name1, {Name Lookup Range 1}, 1))

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Kevin Smith

    Would you be able to post a screen capture of the table you're using for the INDEX(MATCH, along with a screen capture of the sheet where you're inputting the formula?

    The formula itself is built correctly, so I'd like to see the columns it's referencing (Ranges 1 & 2, along with the Name column in this imported sheet). As long as the name in the Name column matches exactly the same value that's in the column Range 1 of the other sheet, it should pull in the correct contact.

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭

    Yes, thank you for taking the time, I thought I had but it doesn't seem to have worked. I'll try a different browser. I appreciate your time, I have read all your other posts on this topic and feel you are the expert!

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/15/20

    Haha no problem! I'm definitely not the expert but I'm happy to try and help figure out what's going on!

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Answer ✓

    Look up Table

    VLookUp Grid


    Please let me know if these come through.

    Also attaching as files.


    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭

    Thank you so very much. I was right about you being the expert!

    That worked and I don't think I realized exactly how that 1/0 worked.

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Genevieve P.
    Genevieve P. Employee Admin

    I'm so glad that worked for you! 🙂

  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭

    Hi Genevieve,

    I am sorry to bother you again but I am hoping you might be able to explain. I have repeated the test I shared in this chain within the grids that I need to use it as well as repeated it in a brand new test. They are both working but only showing the names and not the true SS contact. I am not sure why it is working in the test that I shared here and not in the others. Do you have any idea why it wouldn't give me the actual contact info. I have the column set up as Contacts and I have tried it with multiple checked and unchecked.


    Thank you

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Kevin,

    I'm happy to help! The formula will return a text value if the column it's looking at is a Contact column {Name Lookup Range 2}, but the column where you've input the formula is a Text/Number column... is that a possibility? Could you double check the column type where you've put the formula?

    If that's not the issue... I'm going to need to ask for more screen captures again, haha. It would be helpful to see a screen capture like the ones above, but with the formula open in the sheet so I can see it lighting up... if you wouldn't mind taking a picture of each range in the pop-up range window that would also help!

    Thanks,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!