Filter by predefined list
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 |
Filter by predefined list
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. -- Regards, OssieMac "ARS" wrote: 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 |
Filter by predefined list
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: 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. -- Regards, OssieMac "ARS" wrote: 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 |
Filter by predefined list
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 -- Regards, OssieMac "ARS" wrote: 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: 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. -- Regards, OssieMac "ARS" wrote: 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 |
Filter by predefined list
Thanks for your reply, but I dont 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 -- -=ARS=- OssieMac skrev: 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 -- Regards, OssieMac "ARS" wrote: 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: 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. -- Regards, OssieMac "ARS" wrote: 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 |
All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com