data validation not displaying all items in range...

[sadams1]

this should be simple but something is off...

I have a single column range with 45,000 rows with text in only 150 of those rows.
Would like to do data validation so a drop down lists only the rows with text.
I set it up by "data" > "validation" but the drop down list doesn't display all 150 rows with data & it looks like less than half of them.

The formatting for the entire range is "general".

Again, this is baffling as it should be straightforward but any insights that I'm overlooking would be great appreciated.
Thanks!
Steve

 

[Micron]

I gather that your validation list is 45K rows and the 150 list items are spread out over all those rows. The max list size is 32,767 AFAIK, so that would seem to be the issue.

 

[sadams1]

interesting...I looked around for constraints like that but didn't find anything. Thank you for the reply! As a side note, if the active cell is in the header row & "Alt + down arrow" is hit, the drop down lists everything alphabetically but not quite what I'm looking to do.

 

[Micron]

Why not put your 150 values in another column (perhaps as formulas based on the other column) as contiguous rows and see what you get?