Auto-complete names from another sheet

Excel 2010 - wanting to add another feature to my .xlsm file.
Sheet1 is the draft sheet, sheet2 has a list of names
When entering the draft pick on sheet1, I want it to auto-complete the name from sheet2 into sheet1

ex. Entering Rodg, it would come up with Rodgers, Aaron

I've attached a sample.

Hi

1)Do you want to type ex. Rodg in column b and appear the name in the came cell? Or we can type Rodg in Column C and Name in Column B?

2) If you entering "Brad", what you want to see?
Bradford, Sam

OR

Brady, Tom?

Then i am sorry... I don't think that you can do this without VBA and in this case, i am not able to help you.

If you want an unique way to do this, try this
- As long as the list of players is immediately above and adjacent to the cell, Excel will autocomplete the typing for the cell {Just what you wanted}
- If you do this, I would hide the rows containing the player list so as not to confuse anyone

There are a couple of problems with doing this:
- There can not be any gaps in the data. A blank row will through this off
- There can not be a blank row between the cells you want and the autocomplete list in the rows above
- The player list and the cells you want have to be on the same sheet and immediately above
- It can make the worksheet messy unless you hide the rows as mentioned above
- You have to keep typing until the selection is unique, ie for Bradford you must type bradf

The main advantage is non programming is required, this is built into Excel

Your data is attached with the approach mentioned above using Data Validation {Data Validation is not required for this to work but it may make what you are trying to do easier

Uploading is not working currently. Here is a link to your file:
https://www.dropbox.com/sh/stnw55n5s...DraftTest.xlsm