I am trying to create a column in which each cell is with a drop down (data validation) where I can choose multiple options foe every cell and then I want to be able to filter the column by individual results.
For instance if column A has the following cells:
A2: apple, orange, grape
A3: orange, apple
I want to use the auto-filter option so that if I filter to show only "orange", I will get A2 and A3. If I filter to show only "grape", I will get A2 and A4.
How can this be done? Does anyone know?
I managed to get the data validation drop down to enable to select multiple values in the same cell, by using VBA code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]' Developed by Contextures Inc. ' www.contextures.com 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 Target.Value = newVal If Target.Column = 3 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else Target.Value = oldVal _ & ", " & newVal ' NOTE: you can use a line break, ' instead of a comma ' Target.Value = oldVal _ ' & Chr(10) & newVal End If End If End If End If exitHandler: Application.EnableEvents = True End Sub
However, I still can't filter it properly as I described in my initial question! Can anyone help? Perhaps I need another code to program the filter?
Thank you very much!!!!