Create name list from multiple sheets based on drop down selection

Thank you so much for the response. That seems to work great, however my work disables macros and we are not able to use them on my work computer. Is there a formula set that can do this same thing?