Data pull from multiple sheets

test run1234.xlsm

Hello,

I currently have a workbook that contains multiple sheets where data is entered on a weekly basis (each sheet contains data for a different person) on a 52 week cycle. What I am trying to do is creat a master data sheet (sheet5) that displays all the data from a particular week from each person depending on the 'week' selected from the drop list. Additionally (thanks to TehneXus), I have a VBA to email the respective line to a person, however it's a set To: address, is it possible to automatically change to the intended recipient from the master data sheet? Where 'week1' would pull the data names from each person to have the email go to that recipient without manually changing the email address.

try this in the master tab

Copy of test run1234.xlsmThanks for your input rcm. I tried entering the values you provided (some cell values I changed to match the appropriate sheet cell ranges) however I was not able to get it to work. I have re-attached the sheet with corrections on my part to fit the proper cell placements.

Sorry rcm, you were right, I was able to get the values to function correctly after all. Thanks! Any thoughts to the "I have a VBA to email the respective line to a person, however it's a set To: address, is it possible to automatically change to the intended recipient from the master data sheet? Where 'week1' would pull the data names from each person to have the email go to that recipient without manually changing the email address. "?