Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Auto-Filter Problems...
At my business we use a certain format for our sales orders. In 2004 we had a format of the job number and the year, such as 4304 or 1704. For some reason, our autofilter cannot seem to sift through this. For instance, after applying our autofilter to the sales order column on our spreadsheets, even using the search 'contains' or 'ends with' 04 won't bring up all the items that contains or ends with 04. Can anyone explain/help correct this problem? -- Kompressor ------------------------------------------------------------------------ Kompressor's Profile: http://www.excelforum.com/member.php...o&userid=24209 View this thread: http://www.excelforum.com/showthread...hreadid=378207 |
#2
|
|||
|
|||
YOu can't use an AutoFilter to find a number within a number.
You can use an Advanced Filter, as described he http://www.contextures.com/xladvfilter02.html#Number Or, add a column to the table, and use the FIND function to test for a number. For example, with '04 in cell F1: =ISNUMBER(FIND($F$1,E2)) Then, filter that column for TRUE. Kompressor wrote: At my business we use a certain format for our sales orders. In 2004 we had a format of the job number and the year, such as 4304 or 1704. For some reason, our autofilter cannot seem to sift through this. For instance, after applying our autofilter to the sales order column on our spreadsheets, even using the search 'contains' or 'ends with' 04 won't bring up all the items that contains or ends with 04. Can anyone explain/help correct this problem? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Debra has probably nailed it, but one other possibility is if you have more
than 1000 unique entries in your data prior to your entries you are expecting, as Excel will only return the first 1000 in Autofilter, eg (from Debra's site):- http://www.contextures.com/xlautofilter02.html#Limits -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Debra Dalgleish" wrote in message ... YOu can't use an AutoFilter to find a number within a number. You can use an Advanced Filter, as described he http://www.contextures.com/xladvfilter02.html#Number Or, add a column to the table, and use the FIND function to test for a number. For example, with '04 in cell F1: =ISNUMBER(FIND($F$1,E2)) Then, filter that column for TRUE. Kompressor wrote: At my business we use a certain format for our sales orders. In 2004 we had a format of the job number and the year, such as 4304 or 1704. For some reason, our autofilter cannot seem to sift through this. For instance, after applying our autofilter to the sales order column on our spreadsheets, even using the search 'contains' or 'ends with' 04 won't bring up all the items that contains or ends with 04. Can anyone explain/help correct this problem? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Filter not working properly | Excel Discussion (Misc queries) | |||
auto filter question | Excel Worksheet Functions | |||
auto filter question | Excel Worksheet Functions | |||
The Auto Filter button lost the column specified option. | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |