autopopulate emails based on the multi choice drop-down selections

OshaK

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!

jmyzk_cloudsmart_jp

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.

OshaK

@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!

jmyzk_cloudsmart_jp

@OshaK

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

https://app.smartsheet.com/b/publish?EQBCT=f7cea94dcb094bffa0b42c189b8648c1

Detailed column information, including formulas

https://app.smartsheet.com/b/publish?EQBCT=a5772f5e6edc477ea0ed827697407d7b

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")

https://app.smartsheet.com/b/form/c06619d504f9418284b4125eec205a54

OshaK

@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.

jmyzk_cloudsmart_jp

Happy to help!😁