Dropdown list with more than eight items

Hi all. I need to create a dropdown list that can handle more than the standard eight rows that data validation allows. My list requires 75 different items in the dropdown. Is there any way that I can do this? I hope someone can help!
JLB.

I presume you're referring to the number of items Displayed as opposed to number of items Contained within the list given a Validation List can hold a large number of options.
If that is indeed the case then I'm afraid you will need to look at using VBA based Controls like ComboBox in which you can specify rows to display.

For an illustration of the above see: http://www.contextures.com/xlDataVal10.html

You would need to copy and paste them. I would also very strongly advise against it. What exactly is the issue with scrolling the validation dropdown list, if I may ask?

I'm afraid so... moreover you need to ensure each has the linked cell updated appropriately.

Perhaps you should think of an alternative approach ?

What about using a User Form such that when the user double clicks on a cell (where you want the list) a Form appears with the ComboBox from which they can make their choice and write back to the cell... this way you have only one combobox to manage.

See attached as a basic illustration of my thoughts...

Just to reiterate, a validation list can hold 70 values it is simply restricted in so far as

a) it can only display 8 rows at any given time

b) it does not have auto complete feature

Re: User Form - VBA is a necessity I'm afraid.

It is a good name though...

The DV dropdown can have 70 rows (and a lot more) it's just that only 8 are visible at one time - you have to scroll to see the others. To be honest, I don't see what benefit you would get from having all 70 visible at once. (though I can see the advantage of the autocomplete, which is tricky to implement with DV.)

Originally Posted by romperstomer

It is a good name though...

Yes, it is... credit to whomever it was that suggested it... ... not getting me much joy on the CV front though !

Originally Posted by romperstomper

...though I can see the advantage of the autocomplete, which is tricky to implement with DV

R, you imply it is "possible" then to implement AutoComplete with Validation - can you demo ?

If you use the file I provided to demo the DV...

in a cell insert a Validation List - set Source to be =Options

You should now see a List in the cell containing the 75 options, 8 will display but you can scroll.

Originally Posted by DonkeyOte

Yes, it is... credit to whomever it was that suggested it... ... not getting me much joy on the CV front though !

yes, it's one advantage of using a real name occasionally!

R, you imply it is "possible" then to implement AutoComplete with Validation - can you demo ?

Not strictly autocomplete, but you can filter the validation list based on what is typed - see here for example. In all honesty I would probably use userform for this instead though, if there were so many items as to require this!

hmm... unfortunately I'm getting malware warning on that link (Chrome)

It's a link to Colo's junk room, so I'm not sure what the issue is. (I have seen it before, but just ignore it, since I don't download or run anything from there.)