The sheet attached is a sample I just whipped up to show the format. There is a long list of programs with the associated engineers assigned to that program. I am looking at the master list and wanting to pull the manufacturing engineer's (ME) name from each program. This is all taking place on a different sheet. I want to have a drop down list for the program that will then retrieve the ME's name and email address so that when changes are made to the item pick list the form can be filled out and will auto-email the changes to the correct ME. The portion of the form I am having trouble with is selecting the correct ME after selecting the program name. The master files contains the latest reassignments and so that would be the file I need to have my drop down list point to. Again the attached sheet is the sample of that master file. The form has been included on that sheet as a visual reference of what I'm trying to make work, one step at a time.

thanks for any feedback. If there is an associated post that I missed that addresses this issue please point me towards it.


sample master file with form.xlsx