Populate/Select a Contact (from a list) depending on the value selected in another cell

I have a problem I can't quite solve and I'm not sure if it's just not possible:

I have a 'Responsible' column that is a list of Contacts. I have another column called 'Site' that is a drop down list. Depending on what 'Site' is selected I'd like the corresponding 'Responsible' to be populated.

Example: If Site 'NYC' is selected then the contacts should be 'NYCtech@email.com'. If the SITE is 'ATL' then 'ATLtech@email.com' and so on.

There's 40 sites so rather than making a very long formula I was hoping I'm just missing the obvious answer.

The Contacts are important as I want to setup automation based on the contact to send reminders, update requests etc.

Thanks!

Find more posts tagged with

Contacts

Accepted answers

All comments

Paul Newcome

You would need to have a reference table set up that has the sites in one column and the contacts in another. Then you could use an INDEX/MATCH like so:

=IDNEX({Contact Column}, MATCH(Site@row, {Site Column}, 0))

Christopher Tirro

Silly question - would a reference table need to be a separate smartsheet or is there a way to do that within this sheet I've created?

Paul Newcome

You can do it in two hidden columns on the same sheet if you want. I general use a separate sheet so I can just hold all of my reference tables together 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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.