Filter Multiple Selections Data Validation

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:

[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!!!!

Re: Filter Multiple Selections Data Validation

forum.ozgrid.com/index.php?attachment/57646/

Hi,

You can look at the attached file. Please refer to sheet "sameCellAddRemove". I would like to filter them so that I will be able to find all the cells containing one of the terms (eg. all cells containing "one"). When I click on the auto filter it just gives me strings of data delimited with commas.