Autofilter Lists Problem

I have a spreadsheet that has 1576 rows and 30 columns.  When doing spot 
checks I switch on the Autofiler and use the drop down list to locate the 
specified value.  In my spreadsheet in one column that I have labelled as 
"Lot" I am unable to locate the specified value.  However, I can scroll down 
and see the value or I can use the CTRL+F function to find the value.

So why am I unable to see the value through the AUTOFILTER drop down list 
when all rows are selected.

This has become very frustrating so any help and advice would be great.  I 
am unable to use the AUTOFILTER dropdown list anymore because I can't trust 
the results.
 
0
Medows (1)
8/15/2005 1:58:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
707 Views

Similar Articles

[PageSpeed] 8

Autofilter will only display 1000 seperate items. One way round it is to use 
another adjacent column with sub categories and filter by that column, then 
the original to keep under 1000 seperate items,
Regards,
Alan.
"Medows" <Medows@discussions.microsoft.com> wrote in message 
news:3AD7607E-9C6F-412E-9D75-46A2E12F3672@microsoft.com...
>I have a spreadsheet that has 1576 rows and 30 columns.  When doing spot
> checks I switch on the Autofiler and use the drop down list to locate the
> specified value.  In my spreadsheet in one column that I have labelled as
> "Lot" I am unable to locate the specified value.  However, I can scroll 
> down
> and see the value or I can use the CTRL+F function to find the value.
>
> So why am I unable to see the value through the AUTOFILTER drop down list
> when all rows are selected.
>
> This has become very frustrating so any help and advice would be great.  I
> am unable to use the AUTOFILTER dropdown list anymore because I can't 
> trust
> the results.
> 


0
alan111 (581)
8/15/2005 2:10:15 PM
An AutoFilter dropdown list will only show 1000 entries. If your column 
has more than 1000 unique items, they can be filtered, but they can't 
all be displayed in the dropdown list.


To AutoFilter for an item that doesn't appear in the dropdown list, you 
can choose Custom from the dropdown list, and type the criteria. Also, 
there are a couple of workarounds here:

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

Medows wrote:
> I have a spreadsheet that has 1576 rows and 30 columns.  When doing spot 
> checks I switch on the Autofiler and use the drop down list to locate the 
> specified value.  In my spreadsheet in one column that I have labelled as 
> "Lot" I am unable to locate the specified value.  However, I can scroll down 
> and see the value or I can use the CTRL+F function to find the value.
> 
> So why am I unable to see the value through the AUTOFILTER drop down list 
> when all rows are selected.
> 
> This has become very frustrating so any help and advice would be great.  I 
> am unable to use the AUTOFILTER dropdown list anymore because I can't trust 
> the results.
>  


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

0
dsd1 (5911)
8/15/2005 2:22:52 PM
Reply:

Similar Artilces:

envelope list from excel
I have a list of names and addresses in an excel work sheet. How can I generate a label list for a mailing from it? Louis Louis Labels from within Excel alone is a trial and error operation. Best to use Word for the mailmerge operation. For help on Word mail merge using Excel as the data source. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm And a training tutorial for creating envelopes and labels. http://office.microsoft.com/training/training.aspx?AssetID...

Create a bar chart from a list?
I have a table, "A1:B10". Col "A" list of names, Col"B" values. How do I create a bar chart that plots the names which only have values? -- Bob "Bob" wrote: > I have a table, "A1:B10". Col "A" list of names, Col"B" values. > How do I create a bar chart that plots the names which only have values? > -- > Bob Have you tried selecting just the cells that have names and values and then going to Insert>chart? You can select multiple cells by holding the CTRl button and the mouse to select. I am not sure ho...

Autofilter
Is it possible to autofilter a single column within a project? I am using Project 2007. Thanks Larry Larry, I am not sure what you mean by Autofilter a single column. Can you give more detail as to what you are trying to do? "External Update" wrote: > Is it possible to autofilter a single column within a project? I am using > Project 2007. > Thanks > > Larry Hello Larry, Sure. Turn on Autofilters and use the drop-down filter only on the single field. The Autofilter icons will appear at each field heading, but you need not use them...

Outlook 2003 Safe/Blocked senders list
Hi, I am working on a custom made spam filtering and I would like to access the Outlook 2003 Safe/Blocked senders list from my application (made in Delphi). Is there a way to do it or it is not opened? Thanks: Peter ...

Problems with crm 3.0 e-mail router.
Hi, I´m having a problem with the crm 3.0 e-mail router service. Suddently i´ve started to have this kind of errors in the event viewer "Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceException: Delivering message: RE: LOG_T01 CRM:00170016 in mailbox: CRM failed (System.Web.Services.Protocols.SoapException: Server was unable to process request. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientPr...

Paste damages formula in autofilter #3
That doesn't seem to work as the paste sends a #REF into the offset o paste so the formula becomes =IF(OFFSET(#REF!,0,-1)>0,1,0) on the righ which returns the #REF error. Think solution would be easier to solve with a macro executin everytime an update occurs maybe?. There's got be a way to do it cause I have seen a worksheet where i works and I don't believe it is likely it was done with macros. (sidenote, database is a central one used to modify other worksheet using macros. Each of these worksheets uses different sets of filters The data pane on the right records subtotal...

how do I add numbers in list of columns
entered numbers in columns of excel and want to total them going horizontal and vertical to get grand total for each column. Can you explain how to do this? For e.g. You enter 1-10 in A1-A10 You enter 11-20 in B1-B10 So, in A11, you type SUM(A1:A10) to get Column Grand total and copy it to B11 to get Grand column total for B And, you type SUM(A1:B1) in C1, to get Row Grand Total and copy it to C2 to C10 to get all Row Grand total Hope this helps. "Amadauss" wrote: > entered numbers in columns of excel and want to total them going horizontal > and vertical to get gr...

Price List #3
We have customers with the same prices, except some items. How can I copy an existing pricelist and change only the items with a different price to assign the new pricelist to the customer? -- Toni Go to Price List Utilities On Sep 25, 5:14=A0pm, Toni <T...@discussions.microsoft.com> wrote: > We have customers with the same prices, except some items. How can I copy= an > existing pricelist and change only the items with a different price to as= sign > the new pricelist to the customer? > -- > Toni This was my first step, but Price List Utilities let you copy only i...

shortcut on autofilter function
i have many columns of data using the autofilter. is there a shortcut that either returns all the fields to the all function or a shortcut that returns all the data to the page thanks One line of code will do this (maybe put in your Personal.xls): Sub Foo() Activesheet.ShowAllData End Sub Then assign it to a New (Macro) Icon in your toolbar. It will always be available on any sheet that has an autofilter range assigned. HTH "derwood" <darren.irvine@gmail.com> wrote in message news:1132654133.294598.246590@g43g2000cwa.googlegroups.com... >i have many columns of data u...

Administrative Time approval problem
Using PS 2007 We have been using Administrative Time for about 1 month with no problem. Starting yesterday (to the best of my knowledge) here is the problem: Team member uses the Plan Administrative Time and placed some hours in the Planned row of Vacation. The Admin Time does go to the Timesheet manager and the Timesheet Manager approves it. Team member goes to the timesheet enters Actuals for that planned time (that appears as a green dot because it was approved). They are not ready to submit, so they just hit Save on their Timesheet. The system changes the dot to ...

distribution list #44
I have a user who created a whole bunch of email distribution list which now need to be moved to a separate "contact" folder. How can I choose all the contacts in a distribution list and move them to a contact list as individuals and not a distribution list. Thanks, Raul Rego NJPIES PS using exchange 2003 and Outlook 2007 you'll need to go through the whole list and pick and choose then move them yourself. Now... if you just need the addresses into individual contacts you can save the DL as a text file (or use Send, in internet format to create a text file) and impor...

AutoFilter and Frequency Function
I am trying to see if I can create a frequency function that will change as I autofilter different criteria. I have a file with over 15,000 rows returning data on sales opportunities including key fields I want to filter on: organization, product line, geography and status (Won, Lost, Open). In each case I want to see the change in distribution of "days in funnel" whether I am looking at all the data or just one geography or just one product line. It appears that the Frequency function does not recognize the filtered out data and continues to return values for the entire data set....

Need Help with Strange problem..
This evening when I open links in my Office 2003 Outlook mail program, it goes to My Documents (as well as the link I want).... How can I get this to stop? Thanks! Terri ...

How do I enter lists into names?
Hello everybody Currently I am using a named range as a list for the data validation function. To make my sheet more idiot-proof, I'd like to enter the items from the named range directly into the name definition (via Insert-Name-Define or VBA). Is this possible? What syntax do I need to use? Greetings from Switzerland Urs 1st - Insert a tab and label it "Departments", that you can hid later put the data that you want in a column, like this: A1= Creative A2= Marketing A3= HR Just keep going down Columns "A" until you hav...

Problem connect SQL from RMS
Hi Frends iam have problems with connect RMS to SQL: Have SMB 2003 Server, SQL 2005 1. With Managment Studio iam access fine to SQL Database but put in SO Administrator: Server: [local] | SQL Server Aut.: User: sa, Pass: same in sql | database: empty (or rmssample) but have these error: Invalid connection, specific SQL Server not found Please Help I think server name should be (local) not [local] Regards, Arthur On 30/05/2010 20:10, RMS wrote: > Hi Frends iam have problems with connect RMS to SQL: > > Have SMB 2003 Server, SQL 2005 > > 1. Wi...

problems with password list
I'm having difficulty with Outlook 2002 and saving email account passwords. Even when I prompt the application to save my passwords, I always have to input the email address again when I open Outlook later. What's going on? For Outlook 2000 or 2002 on Win98, NT, or 2000: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q290684 For Outlook 2000 on Windows 2000: (CW) http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q275465 (IMO) http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q259416 For all Outlook versions on Windows XP (just ignore the parts that refer ...

Pivot table problems #2
Hi I created a pivot table from several sheets, using the multiple consolidation option. My data is all the same with headings: Name Nationality 1936 (Note this is points scored, but the pivot will show points for the year 1936, 37 etc, then sum them) Fred England 15 etc etc The pivot shows Page1 All Sum of Value Column Row 1936 1937 Nationality Grand Total Fred 15 6 0 21 etc So, I really want the Nationality to show 'England' and not 0 and be alongside riders name In the Pivot Table Field List picker all I...

My Reports List
In Great Plains 10, I've added 12 reports to the My Reports List on my home page. I can only see 10 of them, but I have a "More..." option at the bottom of the list. When I point the cursor at that "More..." option, the text turns blue and is underlined. When I click on it, though, nothing happens. How do I see the rest of the reports in my list? Thanks in advance! Mary Fetsch On Jul 29, 10:20=A0am, Mary Fetsch <MaryFet...@discussions.microsoft.com> wrote: > In Great Plains 10, I've added 12 reports to the My Reports List on my ho= me ...

problem in editing imported file
As I have done many hundreds of times before, but not on this computer, I was trying to edit some data this afternoon. Its in a very messy format, so I just imported the text, line by line. Then I planned to parse the line. Imagine my surprise when I indicated a fixed width of two for state, and found out that Texas is "T". I finally did a workaround, and parsed off three places to get two, but find this very peculiar. Also - when I got done, some of the columns wouldn't size down; they seem to have spaces at the end, but when I edit/erase the spaces, the text starts to ...

how to match between two list using letters
Hi, I have two sheets as follows: sheet1 A--------------B 34145 D173abcd 333215 TI242EFG 344233 E0124ABCD Sheet2 A-----------B------------C D-173 abcd ? B-241 efgh ? TI-242 XYZ ? E-0124 STQ ? What I want to do is taking the value from Colum A in sheet1 and put it in column C in sheet 2 using Sheet1 Column B as an indicator. Please note that the Column A in sheet2 has the same data of Column B in sheet1 but there are extra letters and characters. In C1 of sheet2 and copy dow...

list function
I have 10 student names put in from cell a1 to a10 in sheet1, and their age put in from b1 to b10. I created a list of student names by selecting cell a1 to a10. I go to sheet2, cell a1: I created a drop drop list of student name. I want cell b1 in sheet2 to auto pop up the age of whoever I choose from the drop down list in cell a1:sheet2. The age is according to the age entered in sheet1. What is the exact formulas for me to this in cell b1:sheet2 or is this possible at all? =IF(A1="","",VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0)) -- Regards, Peo Sjoblom &q...

PROBLEM SIZING A TEXT BOX IN A FORM
Access 2007/vista I have a form in which I cannot adjust the text boxes INDIVIDUALLY. I click on a text box and it becomes surrounded by an orange border, indicating it is selected. But when I size it smaller or larger, all the other text boxes in the form move in tandem. I have not encountered this before and am baffled. Ideas? -- William ...

Problem with XP
HI ALL, Last time, i have to face a very strange runtime error. I have developed an application using VC6 and windows 2000 server. When I run this application on XP, and when drag a DIALOG and is moved off the right corner of application, a runtime error occur and application closes Regards, Ghazanfar Ali Ghazanfar Ali wrote: > HI ALL, > Last time, i have to face a very strange runtime error. I have developed > an application using VC6 and windows 2000 server. When I run this > application on XP, and when drag a DIALOG and is moved off the right corner > of applic...

Lookup and Reference Lists
Here's the situation. In Sheet1 I have a list of about 1200 items in following format: T0000 ---- AAA 00000 BBB 11111 CCC 22222 T0001 ---- AAA 11111 DDD 33333 and so on. In Sheet2, as follows: T0000 T0500 T0700 AAA BBB CCC DDD T0003 T0100 T0900 T1000 T0350 AAA BBB CCC DDD There are about 300 of these blocks in Sheet2. And my task is to fill out the blank cells under each id numbers in Sheet2 with their corresponding values (if any) in Sheet1. I am fairly new to Excel and don't have a clue as to how to go about this...

Folder File list disappears.......
I have what might be a unique situation, or perhaps one I've just not seen reported in these forums before. I've just re-installed WinXP on my main notebook. Given the number of apps that had to be reinstalled, and the various specialized drivers that were needed, I'm not certain exactly when this problem began, but nonetheless..... Not always, but often, when I double click to open a folder, the folder opens, I can see the file list, but then the folder screen clears. If I leave it as such (and often there seems to be no other option) the files refresh after a ...