Filter by predefined list

[ARS]

I need help to create an filter and hope some of you skilled people can help
me.

I have a list that is used to insert selections in cells. I can add several
selection to each cell, and each selection are separated by comma.
I need an filter (autofilter look-Alike) that utilize the same list in a
dropdown list as filter input.

MyList
txt1
txt2
txt3

Cell C3: txt2,txt3
Cell C4: txt3
Cell C5: txt1,txt2,txt3

Filter "txt2" should give row 3 and 5 as result

 

[OssieMac]

Use Autofilter and when you click the DropDown arrow select Custom
Then select Contains
Then enter txt2

This post is in a programming section so if you need the code then record it
for the syntax.

 

[ARS]

I know, but the list may contain more then 20 selections and some of the
selections will have more then 20 characters. The worksheet will be used
frequently by several users, and I would like the user to se what selections
he can filter from without remembering the list.

-=ARS=-


OssieMac skrev:

 

[OssieMac]

Not sure that I really understand what you require so lets try to ascertain
if I do.

Do you mean that you want a DropDown list to select the initial criteria
like txt2 and then you want the AutoFilter to use that selection to apply it
to another table of data so that it displays all that contain txt2?

If above assumption is correct then set up Data Validation on a cell and set
it to a list of the options. (I am assuming you can do that but if not then
get back to me)

Set AutoFilter on for the range of data that you want the above selection to
be applied to. (I am also assuming you know how to do this)

Use a worksheet change event to detect a change in the cell with the data
validation and then use the data to apply the Autofilter criteria. You can
use the following code for the WorkSheet Change event to apply the the
selection to Autofilter.

Not sure if you know this but just in case.
To insert the worksheet change event right click the worksheet tab name and
select View Code and paste the code into the VBA editor keeping the sub name
that I have used. (Alt/F11 toggles between the VBA editor and the worksheet.)

Private Sub Worksheet_Change(ByVal Target As Range)

'Range("D1") is the cell with the Data Validation Drop Down
If Target.Address = "$D$1" Then
Dim strSelection As String

'Edit "Sheet1" to match your worksheet.
With Worksheets("Sheet1")
'Test if AutoFilter is turned on _
Otherwise produces an error in the code
If .AutoFilterMode Then

'Following line sets all filter selections to All _
(Might not be required. Depends on what you need)
.ShowAllData

'Create a string with concatenated wildcards.
strSelection = "*" & Range("D1") & "*"

'Set autofilter to DropDown selection (with wild cards)
ActiveSheet.AutoFilter.Range.AutoFilter Field:=1,
Criteria1:=strSelection

Else
MsgBox "Autofilter is not turned on"
End If
End With

End If

End Sub

 

[ARS]

Thanks for your reply, but I don’t think it meet what I am looking for
I will try to specify my need in more detail.
My worksheet is a list of available consulting engineers. The columns
contain different information of each consultant, and the column in question
contains the discipline of competence. I am using some VB code (shown below)
to add records into the cells of this column. The content of one cell could
typically look like “Administration, Cost control, Business development,
Project management, Instructorâ€. Data validation is used for inserting each
competence utilizing the list “Competence†from another worksheet.

As the Autofilter gives all combinations of competence to choose from, I
would like to limit the filter selections to the content of the list
“Competenceâ€. Hope this clarify what I am looking for.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If Target.Column = 6 Or 7 Then

'Delete content
If newVal = "" Then
Target.Value = newVal

'First entry
ElseIf oldVal = "" Then
Target.Value = newVal

'Duplicate entry
ElseIf oldVal Like ("*" & newVal & "*") Then
MsgBox "Duplicate entry"

'Not first entry
Else
Target.Value = oldVal & ", " & newVal
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub