Linking multiple workbooks based on a specific user name...

First time poster. Go easy, please

I have 3 total workbooks. One is a master workbook called OVA. This is to be used to display information gathered from the other 2 workbooks, called A-M.xls & N-Z.xls. The latter 2 workbooks contain numerous worksheets. Each worksheet represents an employee, with their name appearing on the respective tab and in cell G7. All of the worksheets were created by a template and are identical in relation to cells and formats. Unique user data is inserted into specific locations on each workbook. The workbooks are split into the two groups based on the users last name (i.e.- Smith, Joe would have his worksheet in the N-Z.xls workbook. Doe, John would have his worksheet in the A-M.xls worksheet).

I need to be able to pull user specific information from their workbook, based on a user name typed into a cell on the master workbook (OVA). If I typed Smith, Joe in cell C2 on the OVA master workbook, I would need his information to fill in the appropiate cells on the master workbook. I don't need to open the original worksheet. Ultimately, I will probably have to open multiple users at the same time, and have all of their information list on the master worksheet. I hope I worded this correctly. I know what needs to happen, just not sure how to explain it. Thanks for all the help in advance! I greatly appreciate it!