Limit on data validation list?

[golod1289]

Hey gang,

Is there a limit to how many pieces of data can show up in a data validation list? I'd like to make a list of 50,000 options and can't seem to get more than half of those on my list. Currently have all items with the range name as "title" and that's what I've put as the range for the table.

Any help would be greatly appreciated!

 

[JoeMo]

I'm unaware of an official limit on this, but from what you have observed I'd guess the limit to the number of items on a validation list may be the same as the limit to the number of characters a cell can contain. In Excel 2007 & 2010 this is 32,767 characters.

 

[golod1289]

Thanks for the reply, Joe.

Are there options for lists that are as long as I mentioned, outside of using data validation?

Thanks again.

 

[JoeMo]

Thanks for the reply, Joe.

Are there options for lists that are as long as I mentioned, outside of using data validation?

Thanks again.

There's probably a way to "limit" a cell entry to a list of 50,000 items with VBA, but it seems rather impractical to require a user to sift through 50,000 items in this manner.