Results 1 to 7 of 7
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Bumped into this today (you live and learn)

    If I have a list (say like the one below) with the Data Formatted to 4 Dec Places

    [attachment=89212:FilterFormattedCellList.jpg]

    Then Apply an equals Number Filter as Shown below

    [attachment=89213:FilterFormattedCellEqual.jpg]

    It returns NO Rows

    But If I Use Between

    [attachment=89214:FilterFormattedCellBetween.jpg]

    It is Correctly Filtered

    Is an = Number filter meant to be Format Sensitive (seems odd)
    Appears to be consistent in all versions of Excel 2003 - 2010

    If you run Advanced Filter against the Unformatted Data it is fine.

    I could just be having a bad day, and have missed something really simple (It is happening more often).
    Attached Images Attached Images
    Andrew

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Interesting, I run into something similar with Conditional formatting (I thought it was just a 2007 Conditional Formatting thing).
    I am going to play with it some more now...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just as a matter of interest here is the workbook I tested it in.

    [attachment=89216:RandomFilterVals.xlsm]

    And Also, IF you select the value in the list rather than using a custom filter it is fine
    (as you might expect because the drop down list is the formatted values)
    Attached Files Attached Files
    Andrew

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    This is curious!
    I expanded col O and put =round(Nx,4) where x is the row number and copied it down.
    It still won't match on 45.61 but will match on 45.6100

    Go figure.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    It looks like using "equals" in a Custom Auto Filter is format sensitive. The "equals" must refer to something that exists in the drop down list, unless using the wild cards such as ? or *

    To test this in your sample file....
    Change format of Cell N7 to Currency with 4 decimals
    Change format of cell N192 to Number with 2 decimals

    Now, when the Custom Auto Filter uses "equals", there will be 3 choices in the drop down list for 45.61
    $45.6100
    45.61
    45.6100
    Your resulting filtered data will vary depending on which of the 3 is chosen.

    Hope this helps.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That's my point really.
    Why is a Custom Filter on a Numeric Column
    (In 2007 and 2010 it is actually defined as a number filter)
    Format sensitive when using equals

    IF you use >=, <= it works just with the numeric value in the cells.
    With = I would argue, it is NOT a number filter.
    Me thinks that MS ought to fix it as a patch,
    OR provide the option to filter on value or formatted value,
    A bit like Find.

    They also limit the number of entries in the AutoFilter drop down to 1000 in 2003, although that is up to 10,000 in 2007
    Andrew

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by AKW View Post
    With = I would argue, it is NOT a number filter.
    Me thinks that MS ought to fix it as a patch,
    OR provide the option to filter on value or formatted value,
    A bit like Find.

    They also limit the number of entries in the AutoFilter drop down to 1000 in 2003, although that is up to 10,000 in 2007
    I agree for a Custom Filter that an option to filter on value and/or formatted value would be very helpful. Both ways have useful features depending on what the user wants to filter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •