Data Validation & Advanced Filter On Dropdown

    Hi all,


    I need help! I created a dropdown menu using Data Validation and a dynamic range, so that anything typed into the range automatically appears on the dropdown.


    The formula I used for this was a Defined Name of:


    =OFFSET('Priority List'!$C$6,1,0,COUNTA('Priority List'!$A:$A),1)


    Where C6 is the header cell of the column and I want the range to go on to the end.


    What I want to know is how I can filter that dropdown so that when I select "A" from the dropdown menu, all the rows that do not have "A" in the C-column are hidden (like what happens with Autofilter). Is there any way to do this?


    I am pretty new to the tough aspects of Excel, so please try to keep it relatively simple.


    Thanks!

    Re: Data Validation & Advanced Filter On Dropdown


    See [dr]*[/dr] here. AdvancedFilter here. I think your range should be definded as
    =OFFSET('Priority List'!$C$6,0,0,COUNTA('Priority List'!$C$6:$C$65536),1) so that the heading IS part of the range.


    Let's say your Data Validation list is in Cell C1 and the Filter range is C6 onwards with C6 being the heading.


    in C2 Enter: Criteria
    in C3 Enter: =ISERR(FIND($C$1,UPPER(C7)))
    Name the Range C2:C3 Criteria


    Now, after choosing you letter from C1 run this macro.

    Code
    Sub FilterBy()
   Range("MyRange").AdvancedFilter xlFilterInPlace, Range("Criteria")
End Sub

    and this one to show all data

    Code
    Sub TakeOf()
   ActiveSheet.ShowAllData
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login