Hi,
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:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
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:
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!!!!
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:
A1: header
A2: apple, orange, grape
A3: orange, apple
A4: grape
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:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
[COLOR=#333333]' Developed by Contextures Inc.[/COLOR]
[COLOR=#333333]' [/COLOR]
[COLOR=#333333]Dim rngDV As Range[/COLOR]
[COLOR=#333333]Dim oldVal As String[/COLOR]
[COLOR=#333333]Dim newVal As String[/COLOR]
[COLOR=#333333]If Target.Count > 1 Then GoTo exitHandler[/COLOR]
[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)[/COLOR]
[COLOR=#333333]On Error GoTo exitHandler[/COLOR]
[COLOR=#333333]If rngDV Is Nothing Then GoTo exitHandler[/COLOR]
[COLOR=#333333]If Intersect(Target, rngDV) Is Nothing Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]newVal = Target.Value[/COLOR]
[COLOR=#333333]Application.Undo[/COLOR]
[COLOR=#333333]oldVal = Target.Value[/COLOR]
[COLOR=#333333]Target.Value = newVal[/COLOR]
[COLOR=#333333]If Target.Column = 3 Then[/COLOR]
[COLOR=#333333]If oldVal = "" Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]If newVal = "" Then[/COLOR]
[COLOR=#333333]'do nothing[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]Target.Value = oldVal _[/COLOR]
[COLOR=#333333]& ", " & newVal[/COLOR]
[COLOR=#333333]' NOTE: you can use a line break,[/COLOR]
[COLOR=#333333]' instead of a comma[/COLOR]
[COLOR=#333333]' Target.Value = oldVal _[/COLOR]
[COLOR=#333333]' & Chr(10) & newVal[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]exitHandler:[/COLOR]
[COLOR=#333333]Application.EnableEvents = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]
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!!!!
Last edited by a moderator: