Highlighting the value in dropdown list while typing the required value

Hi All,

I'm finding difficulties in selecting value in data validation list i.e., I've dropdown list with about 30 values in which If I have to select any value that is in the middle of the list, I've to go through all the list to select it.

Solution I need is..If I type any word in keyboard, is there any way we can highlight the word that I'm looking for

I dont want highly technicality such as search suggestion, this is the functionality which we can see in any of the web forms. So can we implement to our data validation list

This page explains how to do it.

http://www.ozgrid.com/Excel/autocomplete-validation.htm

Thanks for your reply Jason.

As per your link, Auto-Complete option is already enabled in my excel where as I didnt understand about the "Move Your List Of Items Is In The Range Immediately Above The Validation Cell."

Could you please elaborate it or I've attached the excel file. Can you please help me out in making this happen.

I'll only open uploaded workbooks, not zip files.

Assuming some basic facts about your sheet, which you can adjust to match your real layout.

You have column headings in row 1
Your data validation dropdowns are in column D (starting in D2)
Your validation list contains 20 items.

First insert 20 empty rows above your column headings, so that the headings are now in row 21 and your first data validation dropdown is in D22.
Copy your list of items used for validation into D1:D20, in the order that you want them in the list if you click the dropdown arrow.
Use D1:D20 to fill your validation dropdown.
Hide rows 1:20

Start typing in D22.

A couple of things to note.

1. It will not work if there are any empty cells between the D1 and the cell you are typing into. (Blank cells, i.e. ="" or any formula that returns "" should still work ok).
2. You cannot use auto complete and the dropdown together, only one or the other, although autocomplete entries will be validated against the list. (there is no workaround for this, if you want both then you will need to use Tony's activex combobox suggestion).

I think this is right, I haven't needed to set this up in a sheet since 2008 and don't have time to test now. Hopefully somebody will correct any errors if needed.

Originally Posted by jason.b75

I'll only open uploaded workbooks, not zip files.

Assuming some basic facts about your sheet, which you can adjust to match your real layout.

You have column headings in row 1
Your data validation dropdowns are in column D (starting in D2)
Your validation list contains 20 items.

First insert 20 empty rows above your column headings, so that the headings are now in row 21 and your first data validation dropdown is in D22.
Copy your list of items used for validation into D1:D20, in the order that you want them in the list if you click the dropdown arrow.
Use D1:D20 to fill your validation dropdown.
Hide rows 1:20

Start typing in D22.

A couple of things to note.

1. It will not work if there are any empty cells between the D1 and the cell you are typing into. (Blank cells, i.e. ="" or any formula that returns "" should still work ok).
2. You cannot use auto complete and the dropdown together, only one or the other, although autocomplete entries will be validated against the list. (there is no workaround for this, if you want both then you will need to use Tony's activex combobox suggestion).

I think this is right, I haven't needed to set this up in a sheet since 2008 and don't have time to test now. Hopefully somebody will correct any errors if needed.

No Its not working for me. I copied the items in the above cells and started typing in the data validation list which is not at all working

Could you please help regarding this.

Originally Posted by laansesu

No Its not working for me. I copied the items in the above cells and started typing in the data validation list which is not at all working

Could you please help regarding this.

Without seeing what you have done I could guess all day and still not resolve the issue.

Please attach your workbook (not a zip file) showing what you have tried. If it is too big to upload then try removing any sheets that are not relevant to the question.

Here is a link to a You Tube vid that I have found useful at times. You cah build a searchable dropdown list using formulas only.

HTML Code: 

Jim O