Hello Smartsheet World!
I am attempting to run a formula that will return contact emails depending on which departments are selected in a multi-select dropdown column.
Sheet 1: Improvement Notes Smartsheet
-Dept Attention column is a multi-select dropdown
-Department Contacts column (in green) is a contact list that allows multiple contacts (this is where I want to add a formula that auto-populates the contacts based on the departments selected in the Dept Attention column)
Sheet 2: Department Contact Guide Smartsheet
- Smartsheet that the formula would reference to lookup which contacts are associated with each department
From my research, I've seen a combination of formulas but am stumped on what direction to take:
=IF(NOT(ISBLANK([Department Attention@row])), INDEX(COLLECT({Department Contact Guide Contacts }, {Department Guide Dept Attention}, HAS(@cell, [Department Attention@row]), 1), " ")
The formula needs to be fixed and I'm unsure if I'm doing this right or if what I'm attempting to accomplish is possible.
Any guidance is greatly appreciated!
-Alexis