Is there a way to tell if a multi-select contact column doesn't contain any real contacts

I am using a form to get some projects input into a sheet, one of the columns is a team members multi-select contact. In the sheet itself you are able to input "real" contacts that contain email and look nice. However, in the form it appears only as a free text field.

I could not prepopulate this column with drop down options since anyone in the company can be listed as a team member and the management of that list would be to complex. The solution we landed on was to do a once a month clean up, but I wanted to try and make it easy and only show items that need to be cleaned. Any ideas on how to do this?

Find more posts tagged with

Accepted answers

All comments

nicksnav

Hi @rbrice!

How is your cell set up on the sheet? I am able to get pre-selected contacts to populate on the form for my contact list column. I allow multiple contacts per cell, restrict to list values only and Add contacts before going to the form.

This is how I have my contact column set up.

ker9

@rbrice

This does not completely answer your question, but it provides a possible solution to the need for an accurate dropdown with names that change.

Smartsheet has a relatively new feature that allows Linked Dropdowns.
Unfortunately, it does not work with Contact names (yet), however, we can overcome that with some work.

User Names
I have a sheet that is updated weekly with the data from the Smartsheet User List (i.e., contains the Smartsheet user names and emails of everyone).

You may need a System Admin to setup and generate the User List. I currently export it to Excel and then use DataShuttle to send it back to Smartsheet.

In the User List sheet I have a formula column (NameEmailFx) that joins the Name with the Email, which will allow creation of a "Contact".
The formula (no blank names): =IF(Name@row = "", "", Name@row + " <" + Email@row + ">")
Looks Like: Jane Smith <Jane.Smith@x .com>

In your Smartsheet:
Create a TEXT multiselect dropdown column that is linked to the NameEmailFx text column in the User sheet.
Use this text column on your form.

Create a CONTACT multiselect dropdown column that will be updated by DataMesh (not used on your form).
Your sheet should also contain a unique ID column to use with DataMesh (use AutoNumber or other unique ID). DataMesh the new Text column to the new Contact column.

Challenges
There may be automation issues to overcome since the contact column will not be updated immediately upon addition of new rows (even if Update Immediately is used in DataMesh, there is a lag).
There may also be a need to overcome users that leave and are no longer options in the dropdown.\

If you do not have DataMesh, there is another option but it would involve a formula and manually updating a column.

As for determining if any of the existing contact names are not accurate, I suggest using DataMesh again to send those Contact names to a Text column. Depending on quantity, you may end up exporting that to Excel to sort through it and separate out the names to compare them to your Smartsheet user list.

Hope this is helpful and not too confusing. :)

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.