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.
Comments
-
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
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.
-
@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.
