Auto Complete on Variable Drop Down list

Hi,

I use drop down lists regularly in my workbooks and found that as a user once you had become familiar with the list, could select the cell that contained the drop down list and start typing the first letters of one of the items in the list and Excel would (in grey) go to the first entry, offering a kind of auto complete. The user could then press enter and the selection would be made allowing the user two ways of obtaining the correct entry.

For the first time today i have created a variable drop down list, a list that is created based upon the value entered in a different cell (Example workbook attached).

Since creating this Excel is sporadic with either auto completing or throwing up the error message 'This value doesn't match the data validation restrictions defined for this cell.'

My questions are: - Is there anything incorrect within my formulas that are causing this or is there something else i can do to stop the error message? and also, is there a way of refreshing the list when the variable is changed as at the moment you change the list variable but the entry from the previous selection remains?

Thanks in advance for your time and help.

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

I think it will be good that you explain your key output that you want to achieve using Data validation (Dropdown),
from our spreadsheet I can't figure out what you want to achieve

Best that give an input (can either key in or using drop down), and the result (report) that you want excel to display,
including explaining you wanted a message (?)

Rgds

A few things

- You don't need separate lists for the data validation. Your table G11:I16 can suffice.
- Ive defined two named ranges

Team =Sheet1!$G$11:$I$11
Selection =INDEX(Sheet1!$G$12:$I$16,,MATCH(Sheet1!$C$11,Sheet1!$G$11:$I$11,0))

These are used in the respective drop-downs.

Lastly, there is an worksheet change code which will celar the contents of Cell C16, whenever the value in C11 changes

Please Login or Register  to view this content.

I'm not sure about the auto-complete issue that you mention, but do note that your range needs to be contiguous (no intermittent blank cells) for the auto-complete prompt to kick in

auto-complete-on-variable-drop-down-list-test.xlsm

Does this help?