+ Reply to Thread
Results 1 to 5 of 5

Multiple Filters ,Advanced Filter and Autofilter

  1. #1
    Registered User
    Join Date
    04-27-2009
    Location
    Newcastle upon Tyne , England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Multiple Filters ,Advanced Filter and Autofilter

    Hi

    Using excel 2003 sp3 on an XP machine.

    using VBA I have a control sheet which summerises variouse counts & totals of data held on a detail sheet. Bu using filters and counting the visable rows.

    Statistics on 50 columns of data held in several thousand (rows) mixed around eight business regionsheld in first column.

    The idea was that I could determin the number of affected rows by using Tick boxes on the control sheet and applying filters to the detail records

    A) checkbox indicates if I need filtering on the type of data in my detail sheet and apply the filter
    Selection.AutoFilter Field:=XX, Criteria1:="Y"

    B) Because I could not have more than two criteria on an autofilter column I resorted to using Advanced Filter on the column with the Business UNIT's,
    I Create a range write the criteria of the records to be filtered into the range, then apply an advanced filter using that range.

    Both of these work well indevidually, but I am getting inconsistant results when I mix them

    using the autofilter route I can select multiple tick boxes and the output is correct, and using the advanced filter I can select any combination of business units and the output is correct, however I cant get them to work together

    Any Ideas please


    Thanks

    as per sugestion will use advanced filter for all criteria
    Last edited by capetownandre; 04-27-2009 at 11:26 AM. Reason: solved

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multiple Filters ,Advanced Filter and Autofilter

    its hard to see without an example but i suspect your criteria are not quite right, probably using an "and" instead of "or" set up. but you probable need to use advanced filter to do the whole thing not try and combine them.

    post a sample showing what you want to achieve. showing results of auto filter,results of advanced filter and what you really want to see
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    04-27-2009
    Location
    Newcastle upon Tyne , England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiple Filters ,Advanced Filter and Autofilter

    cheers

    I have created a cut down version of what I am trying to achieve
    The raw data will be in sheet "Sparedata"

    across the top are check boxes the business units which write to named range on the summary sheet column z, this is used for an advanced filter.

    Then I have multiple selection criteria down the side, ( Only two shown here)the entries are dynamically written as I read a new input files, so I never know what the row numbers will be until I have processed some input

    The dummy selection I have coded against the two check boxes are
    for col5 colour = RED
    and for Col6 = True/false


    I am interested in the count of rows displayed at the top

    I can use the top selection and it works, as soon as I use the ones down the side they overide what was selected above

    So what I am asking, is there some way to filter on multiple columns , and amongst the filtered rows have an advanced filter included
    At the moment they appear to be mutually exclusive.

    Thanks for your time
    Attached Files Attached Files

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Multiple Filters ,Advanced Filter and Autofilter

    this says solved ! is it?

  5. #5
    Registered User
    Join Date
    04-27-2009
    Location
    Newcastle upon Tyne , England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Multiple Filters ,Advanced Filter and Autofilter

    Thanks Martin

    The original problem is solved in that I can now applyfilters as I wish,
    I would ideally like them to have been auto filters, as then when you switch to the detail sheet you could manually adjust the filters to fine tune the returned rows.

    I am attaching a skeleton of what I came up with

    The two filter methods appear to be mutuall exclusive.
    The named range includes all the culumns where there are active filters.

    There cannot be any unused cells in the range, if for example I choose six regions then the named range will have a depth of 6 rows and all the other columns I select for the filter have to be filled to a depth of 6, I simply repeat the selection criteria.


    Andre
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1