Limits to selections in Autofilter drop-down? (Excel 2000)

[P. Johnson]

In this instance, I'm working with a large spreadsheet
(~55K records). The user wants to be able to select
either a vendor name or a tax-identification number (TIN)
from the autofilter drop down list. However, not all of
the selections which exist in the worksheet appear in the
drop-down list.

The number of unique vendors and TINs probably approaches
the total number of records in the worksheet. As a work-
around, I've suggested to the user that they first filter
on a field that has a small number (~50) of unique values,
then to filter on TIN or vendor.

Is there a known limit to the number of selections that
will appear in the drop-down list for an autofilter?
Would a pivot table have the same limitations?

 

[Paul B]

The limit is 1000 entries, for a work around have a look here
http://www.contextures.com/xlautofilter02.html

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 97 & 2000
** remove news from my email address to reply by email **

 

[Max]

The limit in the dropdown is 1000.

Try MVP Debra D's page at:

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

which provides some cool workarounds

 

[Peo Sjoblom]

About 1000 unique records are visible,
a pivot table has not the same limitations. Your workaround
is definitely doable. You could also add help columns, see:

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

 

[P.Johnson]

Thanks to all of you for the info and the contextures.com
link! Have a great weekend.

 

[Max]

you're welcome!
we appreciate the feedback

rgds,
Max