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

Christopher Tirro

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!

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.