Quite specific Excel (dropdown box) question!

BrunoM

Hi all,

Not really moneysaving - if anyone can suggest another forum with the same level of helpful folks I could take this question there

I have an Excel file with Data Validation list dropdown boxes for the user to select names (and avoid mis-spellings etc). This is fine, but the list is pretty long - several hundred names. Scrolling through this to find their choice is annoying.

In other Web and even Excel lists and tables, it's possible to e.g. hit the first character and be taken to that section of the list. This doesn't seem to happen with a (Data Validation) dropdown list. Does anyone have a suggestion as to how I could enable that functionality, or equivalent?

Thanks in advance for any thoughts - posting here is just a long shot really, I'll keep fiddling

f1charlie

Use a combo box:

http://www.contextures.com/xlDataVal11.html


To be able to jump to a selection like you want to, you need to use the combo box from the control toolbox and not the forms toolbox.

BrunoM

Thanks - much more work, but does exactly what I need.

f1charlie

That example is really a bit more complicated than is needed - I couldn't find a simple one, so it may not require as much work as you need.

If you just want a simple selection from a drop down list, this is what I would do:
1. Define your list as a named range.
2. Insert the combo box where you want it on the worksheet.
3. Right click on combo box and select properties.
4. In LinkedCell enter the cell reference where you want your selection to be stored and picked up by your formulae.
5. In ListFillRange enter your range name.

This then replaces the data validation lists.

If you've got any more queries put them here, because I'm still learning, and if I don't know, hopefully someone else will and I'll gain something too!

Hope this helps.