How to fill a dropdown box with filtered data

[Lorna]

I've been struggling for nearly two weeks with this problem.



I have a worksheet that contains a database of available products. On another worksheet I have a form which a salesperson will use with a customer to help find a list of products that meet certain criteria based on asking the customer four questions. The questions are answered by making selections from dropdown boxes which have been created using Data Validation.

1. The first dropdown (cell C3) is used to select a category of products.
2. The second dropdown (cell C19) uses the Indirect function to filter available products based on what category was selected.
3. The third dropdown (cell C23) also uses the Indirect function to display which sizes the products are available in.
4. The fourth dropdown (cell C27) is to display a list of other applicable options, depending on what was chosen in the first three boxes.

The first three dropdowns work perfectly, the problem comes with the fourth dropdown. At this point using cascading data is just way too complicated. So what I've been trying to do is to use an advanced filter to filter the data based on the choices made in the first three boxes. I've done this by creating the following named ranges on the sheet where the list of products are stored: OtherData (Data!$A$1:$D$1762), OtherCriteria (Data!$F$3:$H$4), OtherExtract (Data!$F$7:$I$7), and OtherFiltered (Data!$I$8:$I$38)

I created a macro to perform the advanced filter that filters and copies the unique data to the OtherExtract range. The range OtherFiltered refers to the data I want for the fourth dropdown. So I set the source for the fourth dropdown to OtherFiltered. I attached a button next to the fourth dropdown and attached the macro. I click the button and everything works perfectly.

Now for my problem, how can I make this macro run without having to click a button?

I tried attaching the following code to to the Change event for the worksheet where the dropdowns are located, but nothing in the second part of the code seems to be working.

Private Sub Worksheet_Change(ByVal Target As Range)
 
'PART 1 - This code works and is used to clears contents of dependent 
'dropdown boxes when data changes in previous dependent boxes.
 
    If Not Intersect(Target, Range("C3")) Is Nothing Then
        Sheets("Food Rotation").Range("C19").Value = ""
    End If
    If Not Intersect(Target, Range("C19")) Is Nothing Then
        Sheets("Food Rotation").Range("C23").Value = ""
    End If
    If Not Intersect(Target, Range("C23")) Is Nothing Then
        Sheets("Food Rotation").Range("C27").Value = ""
    End If
 
'PART 2 is supposed to run only when the Other Criteria dropdown box in 
'cell C27 is selected. This is the part that does not work.
 
If Target.Address <> "C27" Then Exit Sub
On Error Resume Next
    Application.Goto Reference:="OtherClearRange"
    Selection.ClearContents 'Clears range of previous extracted data
 
    Application.Goto Reference:="OtherData"
    Range("OtherData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("OtherCriteria"), CopyToRange:=Range("OtherExtract"), Unique:=True
    Sheets("Food Rotation").Select
    Range("C27").Select
End If
End Sub

I hope I've given enough information so you can understand my problem. Any ideas as to why this is not working would be SO GREATLY appreciated.

Thanks in advance

 

[pgc01]

Hi Lorna

The second part will never run. Target.Address will never return "C27" because it returns an absolute address.

Replace the test with one of these:

If Target.Address <> "$C$27" Then Exit Sub
 
If Target.Address(False, False) <> "C27" Then Exit Sub
 
If Intersect(Target, Range("C27")) Is Nothing Then Exit Sub

 

[Lorna]

Thanks for your quick reply.

I added the code as you suggested, but now when I select any of the dropdown boxes I get the following runtime error:

Runtime-time error '1004
Method 'Range' of object'_Worksheet' failed.

When I debug, this is the code it's hanging up on:

Range("OtherData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("OtherCriteria"), CopyToRange:=Range("OtherExtract"), Unique:=True

Now what do I do?

Thanks again.