Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi data validation in Advanced Filter

    Hello to everyone!

    I am working with excel-file where I should make advanced filter by macros. For this advanced filter I should also have an opportunity to chose in one cell couple of the choses. My problem is that when I am making multi criteria data validation, my macros with advanced filter doesn't work. Could somebody help me please?

    Thanks a lot in advance!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,184
    Thanks
    201
    Thanked 781 Times in 715 Posts
    dopikate,

    Welcome to the Lounge as a new poster.

    We'll need a little more information as to what you are trying to accomplish. A sample file would be most useful.
    Also, have you been able to create the Advanced Filter you desire interactively? If so you can just record the macro and then make adjustments to allow for the substitution of selection values. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    first version.xlsmsecond version.xlsm

    Thanks a lot RetiredGreek!

    I will add my file, that you could see what I've done.
    It was everything is good. Advanced filter I made by macro, everything worked. But my responsible changed here mind, so now she wants that in the line of criteria which I am chiding (that's made by data validation) she could chose couple of the variants. For that, as I understood I should write a code for data Validation in Macros. The problem is that I am not good in coding. So after research which I made, I tried to write that code, but on that moment I've got a problem that I can not delete what I choose and filtering doesn't work.

    Please find inclosed two files. First one is the first version of that advanced filtering. Second is after trying to write a code…


    Thank you a lot in advance for helping!
    Have a nice day

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,184
    Thanks
    201
    Thanked 781 Times in 715 Posts
    dopikate,

    I've looked at your first file and you have quite a criteria list there. You are aware that with a criteria each additional line adds another selection? That is to say it will select the record if line 8 or line 9 or line 10...etc. match the data record. If this is what your manager wants that's fine but it is a bit unusual to have so many selection criteria. Also the statement: CriteriaRange:=Range("B7:P8") is incorrect as your criteria only extend to column O and this causes an error since there is no header in column P. You only sent a few records and the criteria selects all the records, once the CriteriaRange is corrected.

    I'll look at the second file and post again. HTH :Cheers:

    Update: I didn't see a difference in the second file?
    Last edited by RetiredGeek; 2014-07-04 at 13:33.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Retired Geek,

    Strange, when I am opening my files there is not this mistakes which you wrote me… + second file is different… In second file I wrote code in Sheet1 about data validation…
    Does it possible that we have a difference because I am working in Macintosh system?
    Sorry that I answered you with delay…

  6. #6
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello RetiredGreek and everybody who is reading this post!

    Finally I achieved of writing a code of multi data validation. But right now I have another problem, my filter doesn't work. Can somebody tell me how I can write code of advanced filter with mention that the list of criteria will be multi?

    Thanks in advance!

    P.S. You can find enclosed excel file with my modifications but filter doesn't work when in cells I am choosing couple of criteria. Thank you!

    Have a nice day!
    Attached Files Attached Files

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you elaborate on what does not work. If add multiple criteria (values from various columns) it filters as expected. [Noter: your validation excludes some criteria from the database).

    If you are talking about putting multiple values in the criteria cell, that will NOT work with advanced filter. It will look for matches that contain all those values (in that order) in the database.

    For advanced filter a single row is equivalent to an AND for each column. Adding rows to the criteria range will make the OR. You can also create formulas to give an OR in a single cell, but then the column Header must not match a column in the table (or it will search for a match of that specific formula!)

    See some examples at:
    http://office.microsoft.com/en-us/ex...005200178.aspx

    So if you want to use datavalidation to put multiple things in the cell, you can't use that cell as part of the criteria, but must extract the information in a form usable for advanced filter.

    Steve

  8. #8
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello, Steve!

    Thank you for fast answer and for link.

    I checked link which you sent me. But I didn't find what I should so…
    I will try to explain better.

    In first sheet I should have two datas with two bottoms that the person which will make a filtering just clicked there and have got a result.

    In first data, second row I made data validation, because I didn't find another way to make similar way of showing criteria for filtering.
    Second data it is just results of filtering. I should make this type filtering of data which is in Sheet "Data Base".

    First time it worked very well. Then my manager told me that she need to have an opportunity to make multi choice of criteria in the same row. Here I've got big problem. Filtering works just in the case when I am choosing something one in one cell, but not multi. As you, Steve, mentioned, it does not read that in cell there is multi criteria.

    Maybe there is another way to write a code of criteria cells something looks like a data validation but something with what advance filter will work?
    Or maybe I should change advance Filter? Maybe I should write as Filter in VBA coding of Excel? If yes, I don't know how…

    Sorry, I was very basic user of excel till this task that's why a lot of things I can don't understand from the beginning…

    Thanks in advance for helping!

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    It seems to me you are making it much more complicated than you need it to be.

    I would eliminate the filter macro and the result sheet and just add autofilter on the database sheet. Then when you select the filter column, you get a pulldown list of all the visible items and you can select or unselect them as desired. There is no need for a macro or to manipulate the datavalidation or try to extract proper ciriteria for adv filter to work. You can even combine this with subtotal functions to get stats on the visible items after filtering.

    Steve

  10. #10
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, I know that it is easier to make just auto filter and forget about coding.
    But I have a situation when you should make this table to much presentable + hide from the beginning a lot of information. The person who will use this data should see just first sheet, chose criteria which he wants to see and just click on the bottom and get the result. This is my mission what I should do with this file… + By another person put new information to the table of data base every time (minimum each week)…

    Thank you for advice. I will try to do something by autofiltering…

    With the best regards and wishing.
    Kate

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    But if the goal is essentially autofilter but not displaying all the data, why not keep the autofilter on the database and use a macro to hide the columns you don't want displayed and unhide when you want to enter in data?

    If you have to (or want to) use advanced filter, you will have to write code or use formulas to create an acceptable criteria. If you want complex ANDs and ORs the coding and formulas will be more complex. [it is not the coding for advanced filter, except to possbily expand and/or contract the criteria range, but for creating the appropriate criteria from however you are allowing the user to select multiple items]. In my mind, the simplest way may be to use adv filter with only a 2 cell criteria (header and formula) but the formula may be a complex AND for different columns and OR using MATCH with lists to check for. You could use datavalidation to create the lists if desired, putting an item desired into each row separately.

    For multiple selections, I would not recommend datavalidation, but would suggest at the very least using a multiselect combobox to more mimic the autofilter type selections. Even a userform may be more appropriate for selecting than the a objects in an excel sheet, since you will have control over it and be able to keep track easier. A lot depends on what you want and need. The more complex you make the demands, the more complex the coding will be...

    Steve

Tags for this Thread

Posting Permissions

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