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!
Answers
-
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))
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!