Formula to auto populate contact column

Hello-

We have a contact column where we select the Lead for an issue. This column is a contact list of our current leads. We can call this column "Key Div Dir."

We then have an approval workflow that sends out requests to other peers of this division director. Each divisions director is assigned in their own column. Doing so allows us to eliminate an entry for the Key Division Director in the workflow by simply deleting them from the review by having no entry in their divisions column.

I'm trying to build a formula that inspects the "Key Div Dir" column for a name, and if that name exists then the entry for that division director would be blank. I.E. we don't route an item for approvals from the same person sending it.

The below formula works, but it doesn't format the resulting individual correctly as you would see if you had selected the person manually.

=IF([Key Div Director]@row = "John Doe", "", "John.Doe@Domain.com")

The Formula works, but doesn't format it as a "Smartsheet user." Instead it only shows the users email address.

Is there a way to get it to format this entry similar to how it would be if it were selected long hand?

Thanks

Hudson

Find more posts tagged with

Accepted answers

All comments

Michael Drayton

Hi @Hudson_TMR would a lookup table help? You could have as little or as much contact data and vlookup over to your main table, if needed. Names are a bit tricky to use as your link between tables, but email addresses or phone numbers are unique. Let me know if I'm point you in the correct direction.

Good Luck!

Hudson_TMR

Thanks for the comment @Alpha Chucky. I think a lookup table would just introduce an unnecessary level of complexity but I gave it a shot anyway (sort of). I setup a separate sheet with the division, and director for each with the column type set to contact list for the directors name, and then used a column reference to a separate sheet to pull the name hoping that it would pull the look up sheet's contact field into the cell as a Smartsheet contact but that didn't work either. This is a .gov sheet, so perhaps that's at play and the version just hasn't been caught up with commercial functionality. Will continue to grind away and see if anything comes to light.

Thanks,

Hudson

Michael Drayton

@Hudson_TMR If I'm understanding you, you're pulling the contact data from a .gov database and that just sounds not easy.

Looking at your formula, =IF([Key Div Director]@row = "John Doe", "", "John.Doe@Domain.com") and what you're attempting to achieve, can you choose seomthing other than name? For example if KeyDivDirector is John Doe, but his email address is John.S.Doe@domain.com or JohnDoe2@domain.com, this could become problematic.

Hudson_TMR

@Alpha Chucky I don't think the name lookup portion of the formula is the problem, that seems to be working (i.e. inspecting the Key Div Dir column for the entry. Key Div Dir is also a lookup contact list, and when selecting John Doe, it appears in the cell as John Doe. The formula is in the appropriate column for the division John Doe is responsible for and correctly returns null "" when John Doe is in the Key Div Dir. What doesn't seem to be happening is any way to have the formula return a Smartsheet formatted contact as if it were selected in the dropdown list for that column. My only option seems to be an email address for John.Doe@domain.com which isn't formatted in the cell as if it were selected from the dropdown list of the available contacts.

It still seems that there should be some formatting/syntax that I can use to have Smartsheet pickup that it were the same as if I had picked John Doe from the contact list in the division cell.

I appreciate the ideas and feedback. The fact that the cell reference to an external sheet didn't link back the contact tells me that it may be the version of the .gov service.

Cheers,

Hudson

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.