Autofilter problem

[Lawman]

I use a spreadsheet (Excel 2002 SP3) for maintaining a database of archived
client files. It has about 3,100 rows and uses Autofilter on each of the 10
columns.

I find that the drop down list from the Autofilter in the column by which
the sheet is sorted only displays around half to two-thirds of the items in
the column below, yet the other columns appear to be complete.

Is this a normal limitation of the Autofilter function, or can it be avoided
by better configuration issue?

Thanks for your help.

Lawman

 

[Pete_UK]

The autofilter pull-down only displays up to 1000 different values for
each column. You can still filter on other values, using Custom etc.
Alternatively, you could use another column - eg to reduce text values
by having =LEFT(A1,1), you could then filter this column by the
initial letter, then your other column would show only the values that
began with that letter.

Hope this helps.

Pete

 

[Lawman]

I'll have to try out this work-round, and many thanks for the advice in any
case.

Never realised that the number of displayed values is limited.

Lawman


The autofilter pull-down only displays up to 1000 different values for
each column. You can still filter on other values, using Custom etc.
Alternatively, you could use another column - eg to reduce text values
by having =LEFT(A1,1), you could then filter this column by the
initial letter, then your other column would show only the values that
began with that letter.

Hope this helps.

Pete

 

[Pete_UK]

You might find some further tips on Debra Dalgleish's site:

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

Hope this helps.

Pete

 

[Eli]

Hello lawman,

Handling long, multi-column Excel file is complicated and requires a lot of
finger work, especially if they are continuously growing.
The alternative is to use Business intelligence tools that connect to your
Excel files and enable you to realy analyze and view the embeded data from
any angle you would like to, and at the same time create Pivots, Charts and
Dashboards, yet witout writing macros or VBA code.
Excel life is much livelier with Business Intelligence tools.
Regards

Eli
(e-mail address removed)



Prism - Enhance your Excel with Business Intelligence Power

www.sisense.com