How to solve this filter issue with drop down list?

[fmatre]

Hi,

Hopefully some sharp brains that could help me out....

I have a dynamic drop down list in B3. The dynamic drop down list in B4 is a result of my choice in B3, and my dynamic drop down list B5 is a result of my choice in B4.
When changing a value in B3 the drop down lists B4 and B5 will be reset with this script:

<code>Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Address = "$B$3" Then      

   Range("B4:B5").Value = "-"         

ElseIf Target.Address = "$B$4" Then
   Range("B5").Value = "-"

End If  

End Sub</code>

<code>
</code>So to my problem.
Under this drop down lists I have allot of data in range from "A9:X200"
I want to filter this list by my choice in those 3 drop down lists. value (TEXT) in "B3" should filter Column range "G11" and down, "B4" should filter "H11" and down and "B5" should filter I11 and down. Could anyone help me
with a solution that works with the existing script in the same sheet.


<code></code>

 

[Col Delane]

This is not fancy, but will give you the idea of what is required - though be warned the code fires and applies the filter whenever any ONE of B3:B5 are changed, so to get the result I think you're after, you need to sequentially update B3, then B4, then B5. (See queries below)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

On Error Resume Next
Set MyRange = ActiveSheet.Range("A9:X200")
Set r1 = ActiveSheet.Range("B3")
Set r2 = ActiveSheet.Range("B4")
Set r3 = ActiveSheet.Range("B5")

ActiveSheet.AutoFilterMode = False

Select Case Target.Address
    Case "$B$3"
        Range("B4:B5").Value = "-"
    Case "$B$4"
        Range("B5").Value = "-"
 End Select


With MyRange
.AutoFilter
.AutoFilter Field:=7, Criteria1:=r1.Value
.AutoFilter Field:=8, Criteria1:=r2.Value
.AutoFilter Field:=9, Criteria1:=r3.Value
End With
End Sub

Note that your existing code, and the above, is NOT dynamic, so if you insert rows or columns such that the location of the key ranges change, the code will NOT. It is better practice to assign Defined Names to these ranges in the Excel interface, and then refer to the Names in the VBA code. It would also be better to return the AutoFilter Field No. dynamically (it is the position/index number of the field within the first row of Autofilter range (i.e. the relative position of G9 in A9:X9 = 3, but if Autofilter range was B9:Y9, the relative position of G9 would be 2)


In regard to the following comment:

Under this drop down lists I have allot of data in range from "A9:X200". I want to filter this list by my choice in those 3 drop down lists. Value (TEXT) in "B3" should filter Column range "G11" and down, "B4" should filter "H11" and down and "B5" should filter I11 and down.

1. Are these filters cumulative/concurrent (i.e. all three are applied simultaneously)?
2. I notice that the range starts in row 9, yet you want to filter from row 11 down. What is in row 10? There should be no blank rows in the data table (e.g. between the headings and the first data row.)

HTH

 

[fmatre]

Hi,

Thank you for responding.

In regard to your questions:

1. All three filters don't need to be applied simultaneously. There is not possible to choose anything from Drop List in "B4" or "B5" before something is chosen in drop list "B3".
My thoughts was that I first choose something in the first drop list ("B4"), then Column "G11" and down would be filtered, after that I can get more detailed listing with
choosing something in the next drop list ("B5"), then Column "H11" and down would be filtered. Same for goes for drop list "B6" and values in Column "I11" and down.
The Column G11 to I11 and down have only text and are a list of product/services my company delivers. Column G11 are listed as "Product area" , H11 are listed as
"Offering level 1" and I11 are listed as "Offering level 2" (this list is is down to specific products or services).

2. In Row 9 I have a merged Header_1 called "Company Offering" (Column G,H and I is merged in Row 9) , in Row 10 I have Header 2 (Column "G" is called "Product Area", Column "H" is called "Offering Level 1" and column "I" is called "Offering Level 2"). From column 11 and down I have the data that I want to have filtered trough my choices in those 3 drop down lists (B3, B4 and B5).


-fmatre-

 

[Col Delane]

There is not possible to choose anything from Drop List in "B4" or "B5" before something is chosen in drop list "B3".

What prevents this from happening?

My thoughts was that I first choose something in the first drop list ("B4"), then Column "G11" and down would be filtered, after that I can get more detailed listing with choosing something in the next drop list ("B5"), then Column "H11" and down would be filtered. Same for goes for drop list "B6" and values in Column "I11" and down.

The alignment of your input fields and columns seems to have changed?

• Is it B3 or B4 that sets the criterion for column G?
• Is it B4 or B5 that sets the criterion for column H?
• Is it B5 or B6 that sets the criterion for column I?

Change the references in the code below to suit. (This is where assigning Defined Names to the relevant ranges overcomes the issue of VBA not changing when the location of cells in the spreadsheet change.)

In Row 9 I have a merged Header_1 called "Company Offering" (Column G,H and I is merged in Row 9) , in Row 10 I have Header 2 (Column "G" is called "Product Area", Column "H" is called "Offering Level 1" and column "I" is called "Offering Level 2"). From column 11 and down I have the data that I want to have filtered trough my choices in those 3 drop down lists (B3, B4 and B5).

I would strongly recommend that you do NOT merge cells when working with data lists/tables (or generally within Excel - they cause too many problems). Unless it is only columns G:I that need filters the primary column headings must be located on the same row (row 10) for Autofilters to work properly. You can have a higher level heading above the primary headings, but each column should have a single unmerged cell heading immediately above the first data row.

If each input / filter combo is to work independently, the code needs to change to the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

On Error Resume Next
Set MyRange = ActiveSheet.Range("A9:X200")
Set r1 = ActiveSheet.Range("B3")
Set r2 = ActiveSheet.Range("B4")
Set r3 = ActiveSheet.Range("B5")

ActiveSheet.AutoFilterMode = False
MyRange.AutoFilter

Select Case Target.Address
    Case "$B$3"
        Range("B4:B5").Value = "-"
        MyRange.AutoFilter Field:=7, Criteria1:=r1.Value
    Case "$B$4"
        Range("B5").Value = "-"
        MyRange.AutoFilter Field:=8, Criteria1:=r2.Value
    Case "$B$5"
        MyRange.AutoFilter Field:=9, Criteria1:=r3.Value
 End Select
End Sub

Test this out and come back with results.

 

[fmatre]

What prevents this from happening?

Not easy to explain since i'm not an expert and but got it working by checking different forums . I have made different tables in different sheets that filter out a list of based on my values in the drop list above. I used INDEX to solve this. To filter a dynamic list that depends on what I selected in my first drop list (B3), I entered the following code and copied that formula down the column:
=INDEX(DynamicOffering1;MATCH(0;COUNTIF($E$1:E1;DynamicOffering1)+(DynamicArea<>Segment!$B$3);0))

the same for my last drop list in B5, that is dependent on my selection in B4, that formula looks like this:

=INDEX(DynamicOffering2;MATCH(0;COUNTIF($E$1:E1;DynamicOffering2)+(DynamicOffering1<>Segment!$B$4);0))

After this I made Defined Names for each of those tables and used OFFSET to sort out those rows that is filled out with none valuable data.
I used the following formula for the range in unique filter (Unique Offering1 sheet):

=OFFSET('Unique Offering1'!$E$2;0;0;COUNT(IF('Unique Offering1'!$E$2:$E$40="";"";1));1)

And for the unique filter in Unique Offering2 sheet is used the following formula:

=OFFSET('Unique Offering2'!$E$2;0;0;COUNT(IF('Unique Offering2'!$E$2:$E$40="";"";1));1)

In droplist B4 I used Data Validation and linked to the Define Name. For B4 it is "=UniqueFilterOffering1" and for B5 it is "=UniqueFilterOffering2"

The alignment of your input fields and columns seems to have changed?

• Is it B3 or B4 that sets the criterion for column G?
• Is it B4 or B5 that sets the criterion for column H?
• Is it B5 or B6 that sets the criterion for column I?

Sorry my fault. B3 is the first drop list, then B4 and B5. B3 is for column G, B4 is for column H and B5 is for column I.


Your recent proposal of a solution seems to work almost 100%. Just one issue. When i select something in my first drop list it filters column G, but when I then select something in the next drop list (B4) that I know matches some values in column G, everything seems to disappear. When I then select something in my last drop list (B5) the filtering is back and my values that match my selection in B3,B4 and B5 is back.

Again thanks for the great help!

 

[Col Delane]

As the code enters "-" in B4 & B5 if B3 is selected/updated, and enters "-" in B5 if B4 is selected/updated, this will overwrite any existing value/formula. I also assume that this value is acceptable, as your Data Validation (DV) rules do NOT get triggered when a cell is updated by DV.


As you indicated that B4 is related to column H, as per

... B3 is for column G, B4 is for column H and B5 is for column I.
... When i select something in my first drop list it filters column G, but when I then select something in the next drop list (B4) that I know matches some values in column G, everything seems to disappear. When I then select something in my last drop list (B5) the filtering is back and my values that match my selection in B3,B4 and B5 is back.

that is the column that this code filters:

Case "$B$4"
        Range("B5").Value = "-"
        MyRange.AutoFilter Field:=8, Criteria1:=r2.Value

Field 8 among A,B,C,D,E,F,G,H,I is H. Accordingly, I wouldn't expect there to be any records displayed if the value you entered matches values in column G (instead of H)??
I would also expect that the DV rules in each of B3, B4 & B5 would only allow you to select/enter values that are similar/match existing values in G, H & I respectively - but as I don't know enough about your data, this could be wrong.

 

[fmatre]

Sorry for yet an unclear description from me

Yes B3 is for column G, B4 is for column H and B5 is for column I.

There is no equal data between the columns G,H,I. When I start my selection in those 3 drop list everything seems ok on the first drop list (B3), any selection here that matches my data in column G get filtered. After that I select something in my second drop list (B4) that is based on my selection in my first drop list (B3). Still if I choose something in drop list B4 that I know should match some data in column H, everything disappear. What i would like to happen is that rows that matches my selection (B3 = column G data, and B4 = column H data) should be visible, but every matches also get filtered out. When I then select something in my last drop list (B5) that should match my data in column I seems to be working, but then data data that does not match from column G and H is showing. Seems to me that the visible filtering now just look at matches between B5 and column I.

Lets say i have table of product combinations

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]AREA[/TD]
[TD]Offering Level 1[/TD]
[TD]Offering Level 2[/TD]
[/TR]
[TR]
[TD]Computers[/TD]
[TD]Laptop[/TD]
[TD]Lenovo[/TD]
[/TR]
[TR]
[TD]Computers[/TD]
[TD]Desktop[/TD]
[TD]HP[/TD]
[/TR]
[TR]
[TD]Mobilephones[/TD]
[TD]Galaxy[/TD]
[TD]GalaxyS[/TD]
[/TR]
[TR]
[TD]Mobilephones[/TD]
[TD]Lumia[/TD]
[TD]Lumia625[/TD]
[/TR]
[TR]
[TD]Mobilephones[/TD]
[TD]Apple[/TD]
[TD]iPhone5s[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Logitech[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Chargers[/TD]
[TD]iPhone5s[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Chargers[/TD]
[TD]GalaxyS[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]

I choose the following in my drop lists:

B3 = Accessories

Filtering is showing the following:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD] Accessories
[/TD]
[TD]Keyboard[/TD]
[TD]Logitech[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Chargers[/TD]
[TD]iPhone5s[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Chargers[/TD]
[TD]GalaxyS[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]

Then I choose something in my next drop list (B4):

B4 = Keyboard

Filtering is then showing matches from the selection in first drop list and second drop list

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD] Accessories
[/TD]
[TD]Keyboard[/TD]
[TD]Logitech[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]

Then I choose something in my last drop list (B5)

B5 = Lenovo

Filtering is showing the following:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]


As you see there is no equal data between row G,H and I. But there could be combination that you see above, where some values is equal in the same column but have different values in the columns before:
Shown here:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]AREA[/TD]
[TD]Offering Level 1[/TD]
[TD]Offering Level 2[/TD]
[/TR]
[TR]
[TD]Computers[/TD]
[TD]Laptop[/TD]
[TD]Lenovo[/TD]
[/TR]
[TR]
[TD]Accessories[/TD]
[TD]Keyboard[/TD]
[TD]Lenovo[/TD]
[/TR]
</tbody>[/TABLE]


Could this be a reason why my selection in B5 in some way "resets" my filtering from B3 and B4 and just filter based on the value in B5. I have some values in Offering Level 1 and Offering Level 2 that are identical, but have different values in AREA coloumn.


My last question, Whats the best way to reset all selection and get all my data back unfiltered?

 

[Col Delane]

Ok, I misunderstood how the filters were to be applied.
Also entering "-" into B4 and B5 when B3 is updated causes a problem if you then wish to use these cells as the filter criterion - as there are no cells in column H and I that contain "-". As a result, the code now just clears the contents from B4 and B5 to leave a blank, and the filter for each column will not be applied if the corresponding criterion cell (B3:B5) is blank. Now you can update B3:B5 in any order and the code will apply filters to all three columns for whatever criteria is in B3:B5 immediately after changing any cell.

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
On Error Resume Next
ActiveSheet.AutoFilterMode = False
Set MyRange = ActiveSheet.Range("A9:G14")
Set r1 = ActiveSheet.Range("B3")
Set r2 = ActiveSheet.Range("B4")
Set r3 = ActiveSheet.Range("B5")
Select Case Target.Address
    Case "$B$3"
        Range("B4:B5").ClearContents
    Case "$B$4"
        Range("B5").ClearContents
 End Select
Application.ScreenUpdating = False
MyRange.AutoFilter
If r1.Value <> "" Then MyRange.AutoFilter Field:=3, Criteria1:=r1.Value
If r2.Value <> "" Then MyRange.AutoFilter Field:=4, Criteria1:=r2.Value
If r3.Value <> "" Then MyRange.AutoFilter Field:=5, Criteria1:=r3.Value
Application.ScreenUpdating = True
End Sub

Let us know if this meets your objective.