Dynamic contact list using reference sheets and Data Shuttle

I have several sheets (>20) that utilize a contact field for which I need to update the options whenever there are changes to our roster. I have difficult contact fields depending on someone's role in the department. My solution, based on suggestions here, is using a regular dropdown as my "contact" field that users interact with. This list is updated with a Data Shuttle workflow from a central reference sheet. Then I use an INDEX MATCH to fill a helper contact field in the background with the appropriate individual which I can base my automations off. I'm trying to determine best practice for managing the reference sheet, which actually has to be several different sheets that will be reflective of the dropdown options based on role. My thought is to create a central sheet with ALL employees that will then copy to separate sheets specific to the role. These sheets are then the basis of the dropdown Data Shuttle. In order to centralize the management of removing staff, I would add linked columns to send the row to an Archive sheet when it is selected on either sheet. Are there any changes you would make to simplify this system, or any issues that I may be overlooking? It seems like the biggest hurdle is creating 20+ Data Shuttle workflows, but that will be a one time investment that would allow this project to scale easily.

Find more posts tagged with

Contacts

Data Shuttle

Dropdown

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Jeremy Singleton

You're on the right track — this is a creative and scalable workaround using Data Shuttle and INDEX()!

A few refinements to consider for simplification and consistency:

Central Sheet Structure Suggestion

To make this more maintainable and scalable, you can create a single central employee sheet where each role is a column, and each column contains a vertical list of email addresses:

Manager	Engineer	Staff
alice@example .com	david@example .com	frank@example .com
bob@example .com	ella@example .com	jmyk@example.com

Using Data Shuttle

Use the central sheet as a source for "Replace Dropdown Options" workflows.

Each column (e.g., Manager, Engineer) can be mapped to the corresponding dropdown column in your project sheets. This approach minimizes workflow duplication and ensures the system's maintainability.

Efficient approach using the Organization Users Report:

If you're a System Admin, you can leverage the Organization Users Report to streamline this process:

Export the Organization Users Report (contains Name and Email columns for all licensed users)
Create a formula to construct Name <email> formatted text: =[Name]@row + " <" + [Email]@row + ">"
Convert this formatted text into Contact-type columns - Smartsheet will recognize the Name <email> format and create valid contact objects
Use this as your central contact directory, eliminating manual contact entry

This approach automatically maintains an up-to-date contact list based on your organization's users, significantly reducing maintenance overhead.

Using INDEX for Contact Assignment

After a user selects an email from the dropdown (e.g., in the Staff field), use:

=IFERROR(INDEX({Contact List : Contact}, MATCH([Staff Email]@row, {Contact List : Email}, 0)), "")

This formula retrieves the actual contact from the Contact List sheet's Contact column, enabling automation features such as notifications and update requests.

Control Center – Global Update Support

If you're using Smartsheet Control Center, Global Updates can complement this setup.

What Global Updates can do:

Add or modify Text/Number or Multi-select Dropdown columns and their options
Add new Contact-type columns, including multi-contact fields with predefined contact options (available since August 2022)
Convert existing columns to multi-select or multi-contact
Modify existing multi-contact column options across all provisioned sheets

Limitations:

Only applies to sheets provisioned via Control Center

https://www.smartsheet.com/content-center/product-news/release-notes/control-center-global-update-multi-select-column

Optional: Smartsheet API or other API based platform

If you're looking to automate the entire pipeline:

Use the Smartsheet API, Bridge, or Power Automate to monitor the central sheet
Programmatically update the dropdown or contact column options
Trigger updates when the roster changes — no manual exports needed

You're building a clean and scalable model that effectively combines structure and automation. Please let me know if you'd like to learn more about this option.

Jeremy Singleton

@jmyzk_cloudsmart_jp These are helpful insights that I might be able to apply in some ways, but there are some additional considerations that influenced my structure:

I cannot use emails for the dropdown as those are based on legal name, making if difficult when someone doesn't use that name around the workplace. My reference sheet uses their name for the dropdown with an email on the same line that could be referenced in the INDEX/MATCH.
Unfortunately I cannot leverage the "Organization Users Report" based on our situation where this is only for a small department and has a mix of internal and external users.

Would it still make sense to utilize a central reference sheet rather than my current model that has a central sheet which disperses to separate sheets for each dropdown list? I'll have to review the process for familiarity, but it seems like I could create separate role based text name columns that update the dropdowns. If there is a name in that role field, it will be present in the respective dropdown. I would only need one contact column on the reference sheet for the email. In order to simplify the UX, I would have a multiselect for the roles and create an automation where name is copied to engineer name column if selected.

I do have access to Bridge through my organization but it hasn't been granted yet so I'm not entirely familiar. It does seem like there's opportunities from what I have seen and read.

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.