autopopulate emails based on the multi choice drop-down selections
Hello,
I need to send email notifications to the faculty members once their residents submit the cases on their behalf.
We have a sheet and the form that the residents have to fill in.
The faculty names are listed in the multi-choice drop-down menu in one of the columns, so the residents have to select one or multiple names.
I know how to auto-populate emails from a different sheet if there is only one Name selected, but I am not sure how to send notifications if multiple names are selected or what to do if "Clinical Instructor or Fellow" or "Not Listed" are selected - we have a different column that allows them to enter the names if they are not listed, but not emails. (we need this setup because of a quick rotation of Clin Instructors)
- Faculty Name 1
- Faculty Name 2
- Faculty Name 3
- Faculty Name 4
- ----------------
- Clinical Instructor or Fellow
- Not Listed
Thank you!
Answers
-
Hi @OshaK
I suppose the column where multiple names are selected is a dropdown list whose cell can have multiple values.
In that case, each name or entry is separated by CHAR(10), so by using TEXT functions like FIND, LEFT, MID, RIGHT, and LEN, you can get each name, including Clinical Instructor or Fellow and Not Listed. (You can get the number of items in multiple dropdown lists by COUNTM.)
Then, if you have a table of names and emails, you can get the emails corresponding to the names.
-
@jmyzk_cloudsmart_jp thank you. do you mean a separate sheet when you say 'table'? do you think you could give me a formula example? Sorry, i'm not following. Thank you!
-
Yes, I mean a sheet with a name and email columns.
I modified the previously created solution to include the convert multiple dropdown list to single values part and get an email from another sheet part. (The previous solution was to extract single contacts from multiple contacts, so the solution copes with up to 20 lists.)
Formula Example
Since the multiple dropdown list has a format of "item1+CHAR(10)+item2+CHAR(10)+item3", for example, we use the FIND function to find the position of the CHAR(10)s and get each item using the MID function. [c1] is the first position of the CHAR(10) and [C2] is the second. So, the item would be:
MID([Multiple Name]@row, [c1]@row + 1, [c2]@row - [c1]@row)
Some of the actual formulas:
Multiple Name: a multiple dropdown list
m# =COUNTM([Multiple Name]@row)
c1 =IF([m#]@row > 1, FIND(CHAR(10), [Multiple Name]@row, [c0]@row + 1))
n1 =IF([m#]@row = 1, [Multiple Name]@row, IF([c1]@row > [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, [c1]@row - [c0]@row), IF([c1]@row < [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, LEN([Multiple Name]@row) - [c0]@row + 1))))
n2 =IF([c2]@row > [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, [c2]@row - [c1]@row), IF([c2]@row < [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, LEN([Multiple Name]@row) - [c1]@row + 1)))
Published demo sheet
Detailed column information, including formulas
To request a copy of the solution
Getting a copy of the solution and examining it yourself would be much easier. So, if you need a copy, please request with this form. (Please choose "Multiple Value Dropdown List to Single Values")
-
@jmyzk_cloudsmart_jp yay, thank you so much for your help! it will take me a while to parse it, but i appreciate your help a lot! Thank you.
-
Happy to help!😁
