Filter Limitations

[Lisa928]

Does anyone know what the limit is for the filter drop down box? I have a list of products (sorted alphabetical) and the filter only shows products starting with A-D. Is the limit by the lines it searches to pull the unique occurrences, or is it by the number out output lines in the drop down?

Thanks in advance!
This message was edited by Lisa928 on 2002-10-10 11:49

 

[Juan Pablo González]

I think it shows up to 1000 unique items, but, using the "Custom" filter, you can filter any item on the list.

 

[plettieri]

Here is an old post that may help


Quote
Posted by Mark W. on November 19, 2001 6:23 AM

If you're refering to the number of rows visible
in the drop down list it's 1000. AutoFilter
will operate on lists greater than 1000 rows in
length, but you must select "Custom" and enter
the value(s) of interest. End quote


pll

 

[Tom Urtis]

OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.

As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.

 

[Juan Pablo González]

On 2002-10-10 11:57, Tom Urtis wrote:
OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.

Yes, VERY picky !

As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.

I didn't say that :wink: (Altough it is true) I said that you could use the "Custom" option that appears in the dropdown list of the Autofilter...

 

[Tom Urtis]

Gotta change my brand of coffee...affecting my vision.

 

[Mark W.]

On 2002-10-10 11:57, Tom Urtis wrote:
OK I'm being picky (too much coffee this morning) :wink: ...

AutoFilter limits you to the first 999 unique values in the field of interest which can appear in the drop down list.

As Juan says, Advance Filtering is the option to turn to if you have more than 999 unique items to filter for.

I'm being equally picky... the limit is 1000 unique entires.

 

[Tom@CPC]

Also...check to make sure there isn't a blank line in your table. The wizard will use the area up to the first blank line for its active range.

 

[Mark W.]

On 2002-10-10 12:10, Tom@CPC wrote:
Also...check to make sure there isn't a blank line in your table. The wizard will use the area up to the first blank line for its active range.

That would depend on the selection at the time of the application of the AutoFilter. If you had selected column D (see below) in its entirety (using the column heading button) the values, {5;6;7}, would appear in your AutoFilter drop down list...

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Field1
2		1
3		2
4		3
5		4
6
7		5
8		6
9		7
Sheet1

This message was edited by Mark W. on 2002-10-10 12:19

 

[Tom@CPC]

Thanks for pointing that out Mark, if you select the columns for the table this will alleviate that issue. Not to many people understand this method of selection though. I find it extremely useful in setting the datarange for my pivottables when the raw data is retrieved from an external database. This makes the range for data dynamic.

I was refering to the situations where a user is just in a cell in the table and selects auto filter.