nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Seeking Workaround for Inconsistent Contact Columns

S.Stone

I have two sheets where I'm trying to match row data with contact columns so I can pull corresponding data. One sheet is showing only the name, the other only the email, so Smartsheet isn't finding a match.

I can't use the "Modified By" column, as that is updated by cell links and automations. And I can't figure out a clean way to setup "contains" searches so it can find results for anyone who may use it (besides the two of us listed below).

I know there are multiple feature requests to change the ways these are displayed within Smartsheet. But until there's an internal fix, any workarounds appreciated!

Find more posts tagged with

Contacts

Accepted answers

S.Stone

I think I've worked out a solution for myself!

This should work for everyone who uses the same email format, as long as the email address includes full names. Since it's only looking at names now, it's able to match it up to the other contact column of just names!

This formula also works with the "Created By" column, so I'll leave a copy of that formula here for anyone else who wanders by for a solution.

=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT([Created By]@row, FIND("@", [Created By]@row) - 1), ".", " "), "_", ""), "-", ""))

All comments

Philip Robbins

Hi Sarah,

Probably the simplest way is to have a separate mapping sheet that has the names and emails in separate columns, then is used to pull one or other to whichever sheet needs it.

This aspect of the contact columns is a pain, so I feel your frustration.

S.Stone

@Philip Robbins there's no easy way to extract that from the contact column, is there? If it's not scalable and dynamic, then we'd have to manually update a separate contact sheet every time we needed to add or remove someone. That means I can't "pack and ship" this solution as a folder to different teams to have them run their own solutions with this as a base, as every team would then need someone to update their own mapping sheets every time a change is needed.

S.Stone

I think I've worked out a solution for myself!

This formula also works with the "Created By" column, so I'll leave a copy of that formula here for anyone else who wanders by for a solution.

=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT([Created By]@row, FIND("@", [Created By]@row) - 1), ".", " "), "_", ""), "-", ""))

Philip Robbins

@S.Stone you're fortunate that your email address format lends itself to splitting out first and last names like that. It won't work for everyone, but I'm glad you've found a solution that works for you. Nice work!

For the benefit of anyone else who looks at this and has the same concern as you over my suggestion. The mapping sheet would be a single, central asset, outside of the folder you're looking to replicate. This means when the folder is replicated, the new copy would still point back to that central sheet. That way you maintain one set of mappings in one place.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.