As far as I know, the data validation dropdown can contain 32767 items.
To create a unique list of items, you could use an Advanced Filter, to
extract the values to another worksheet. There are instructions in
Excel's Help, and here:

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

GEORGIA wrote:
> Oh, that's not the answer i wanted hear.. :-) too bad.
> well...
> thank you for your help.
> Do you know if there a way to group duplicate data in dropdown box?
> so that 'Jan-05' will only shows up one time instead of 20 times?
> Thanks
>
> "Biff" wrote:
>
>
>>Hi!
>>
>>There seems to be a limit as to how many items can populate a data
>>validation drop down list.
>>
>>I put a value in every cell in column A, A1:A65536
>>
>>I then created a DD and used A:A as the source. The DD was empty when the
>>the entire column was used as the source. I then started deleting values in
>>column A in increments of 10,000 until something showed up in the DD.
>>Something finally showed up when the range size was 25,000 entries.
>>So........a drop down will hold at least 25,000 entries.
>>
>>Biff
>>
>>"GEORGIA" <GEORGIA@discussions.microsoft.com> wrote in message
>>news:1BC630D8-93DB-43EE-A556-2A010A3ABE7E@microsoft.com...
>>
>>>yes, there are 64465 rows.
>>>if i put =len(a2) in column "A" (where my name range is based from) then
>>>returns 0, if I put in column b, it returns 6.
>>>the data is exported out from Oracle.
>>>Can I send you the actual file so you can see what i'm looking at?
>>>
>>>thank you
>>>
>>>
>>>"Dave Peterson" wrote:
>>>
>>>
>>>>How many rows of data did you expect?
>>>>
>>>>You said that this:
>>>>=counta(qryATL!$A:$A)
>>>>returned 64465
>>>>
>>>>So there's something in there.
>>>>
>>>>Did you get the data from a web site? Maybe it's some of those HTML
>>>>non-breaking spaces (char(160)).
>>>>
>>>>If you put
>>>>=len(a2)
>>>>and copy down all the column, do you see 0's or do you see numbers bigger
>>>>than
>>>>0?
>>>>
>>>>
>>>>
>>>>GEORGIA wrote:
>>>>
>>>>>No forumals and no blank cells. But just incase I did tried to find
>>>>>and
>>>>>replace, but since there are no blank spaces, it gave me a message
>>>>>stating
>>>>>excel could not find it to repace. Is it possible that dropdown has
>>>>>value
>>>>>limitation?
>>>>>
>>>>>As I stated earlier, If i delete some rows, leaving only 15 rows...it
>>>>>works.
>>>>>
>>>>>Thank You!
>>>>>
>>>>>"Dave Peterson" wrote:
>>>>>
>>>>>
>>>>>>Maybe you have spaces in those "blank" cells????
>>>>>>
>>>>>>Do you still have formulas in those cells? If yes, they get counted
>>>>>>with
>>>>>>=counta(), too.
>>>>>>
>>>>>>Or if you had formulas that evaluated to "" and you converted to them
>>>>>>to values
>>>>>>(edit|copy, edit|paste special|values), you could have some junk left
>>>>>>over.
>>>>>>
>>>>>>If that's the case, you can do this:
>>>>>>select your column(s)
>>>>>>edit|replace
>>>>>>what: (leave blank)
>>>>>>with: $$$$$ (some unique string)
>>>>>>replace all
>>>>>>
>>>>>>Then
>>>>>>edit|replace
>>>>>>what: $$$$$
>>>>>>with: (leave blank)
>>>>>>replace all
>>>>>>
>>>>>>This cleans up those cells.
>>>>>>
>>>>>>
>>>>>>GEORGIA wrote:
>>>>>>
>>>>>>>Yes, you are absolutely right. The date is formated as text
>>>>>>>because I had to
>>>>>>>extract a date 1-26-2005 as "Jan-05". formatting as MMM-YY caused
>>>>>>>the problem
>>>>>>>because doing pivot table, it still shows as MMM-DD-YY. anyhow,
>>>>>>>that was
>>>>>>>another issue.
>>>>>>>I did counta instead of count
>>>>>>>=OFFSET(qryATL!$A$2,0,0,COUNTA(qryATL!$A:$A),1)
>>>>>>>and did the validation, and it is still not showing anything.
>>>>>>>when I did =counta(qryATL!$A:$A) i get 64465, which is the # of
>>>>>>>records.
>>>>>>>
>>>>>>>what else am i doing wrong?
>>>>>>>
>>>>>>>"Dave Peterson" wrote:
>>>>>>>
>>>>>>>
>>>>>>>>Maybe it's not the dynamic range formula that's causing the
>>>>>>>>trouble.
>>>>>>>>
>>>>>>>>If you put:
>>>>>>>>=COUNT(qryATL!$A:$A)
>>>>>>>>in b1 of qryATL, what do you get back.
>>>>>>>>
>>>>>>>>=Count() counts numbers.
>>>>>>>>and maybe you don't have real dates in column A.
>>>>>>>>
>>>>>>>>(=counta() counts alpha or numeric entries)
>>>>>>>>
>>>>>>>>
>>>>>>>>GEORGIA wrote:
>>>>>>>>
>>>>>>>>>I just learned this new function so please bare with me.
>>>>>>>>> I have created name range
>>>>>>>>>=OFFSET(qryATL!$A$2,0,0,COUNT(qryATL!$A:$A),1) ( this is column
>>>>>>>>>with a date)
>>>>>>>>>and named it "qryATL".
>>>>>>>>>I tried to use the validation list so that there will be a
>>>>>>>>>dropdown to
>>>>>>>>>choose the date.
>>>>>>>>>However, i don't know if because there are 64,000 rows, there
>>>>>>>>>are nothing
>>>>>>>>>there on my validation list.
>>>>>>>>>I went Validation, from the list, source: =qryATL.
>>>>>>>>>
>>>>>>>>>so I deleted some rows, and left 15 rows, and I see the list on
>>>>>>>>>the dropdown
>>>>>>>>>box. Is there a limit to the drop down box?
>>>>>>>>>Also, is there anyway to group the dropdown box? for example,
>>>>>>>>>I have
>>>>>>>>>several 1-23-05, i want to have the drop down box show that
>>>>>>>>>once.
>>>>>>>>>
>>>>>>>>>Thank you!
>>>>>>>>
>>>>>>>>--
>>>>>>>>
>>>>>>>>Dave Peterson
>>>>>>>>
>>>>>>>
>>>>>>--
>>>>>>
>>>>>>Dave Peterson
>>>>>>
>>>>>
>>>>--
>>>>
>>>>Dave Peterson
>>>>
>>>
>>
>>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html