Auto Filter

[6467100000]

Hi, I hope someone can help me with this problem.

I am creating an incident log which I need to make searchable for colleagues.

In "sheet 1" I have 5 cells (with fixed dropdown lists) which will contain search criteria once selected i.e.
- Incident Reference Number (D9)
- Impacted Business Area (E9)
- Impacted System (F9)
- Incident Category (G9)
- Incident Status (H9)

In "Sheet 2" I could have anything up to 2,000 representing 2000 incidents

I need to be able to auto filter "sheet 2" based on the search criteria set in "sheet 1"
- Incident Reference Number = Column A in Sheet 2
- Impacted Business Area = Column B in Sheet 2
- Impacted System = Column C in Sheet 2
- Incident Category = Column D in Sheet 2
- Incident Status = Column E in Sheet 2

So i need sheet 2 to auto filter the rows based on the criteria set from the 5 cells in sheet 1

I hope this makes sense and hopefully this is possible.

Many thanks

 

[rcbunting]

Which version of excel?

 

[rcbunting]

Here's how I would do it in 2007. I'm also not entirely sure what you mean by 'fixed dropdown lists' and so I proceeded as though they were just empty cells. I suspect you could bind them to the worksheet.

(1) recognize that the criteria has changed

Private Sub Worksheet_Change(ByVal Target As Range)
 
    'Trigger on a change in the criteria cell.  Some users like this immediate response.  Others find it tedious and would rather click a button when all the fields are set
    If Not (Intersect(Target, Range(Cells(9, 4), Cells(9, 8))) Is Nothing) Then
        Call quickFilter
    End If
 
End Sub

(2) do the autofiltering

Sub quickFilter()
    Dim Number As Variant
    Dim Area As Variant
    Dim System As Variant
    Dim Category As Variant
    Dim Status As Variant
 
    'Clear the current autofilter.  I'm assuming Excel 2007 or later and your table on Sheet 2 is 'formatted as a table' and named "Log"
    Worksheets("Sheet2").ListObjects("Log").AutoFilter.ShowAllData
 
    'Capture filter data
    Number = Cells(9, 4).Value
    Area = Cells(9, 5).Value
    System = Cells(9, 6).Value
    Category = Cells(9, 7).Value
    Status = Cells(9, 8).Value
 
    'Apply each filter.  Empty cells will make the macro bomb, so skip the filter in that case
    If (Cells(9, 4).Value <> "") Then
        Worksheets("Sheet2").ListObjects("Log").Range.AutoFilter Field:=1, Criteria1:=Number
    End If
 
    If (Cells(9, 5).Value <> "") Then
        Worksheets("Sheet2").ListObjects("Log").Range.AutoFilter Field:=2, Criteria1:=Area
    End If
 
    If (Cells(9, 6).Value <> "") Then
        Worksheets("Sheet2").ListObjects("Log").Range.AutoFilter Field:=3, Criteria1:=System
    End If
 
    If (Cells(9, 7).Value <> "") Then
        Worksheets("Sheet2").ListObjects("Log").Range.AutoFilter Field:=3, Criteria1:=Category
    End If
 
    If (Cells(9, 8).Value <> "") Then
        Worksheets("Sheet2").ListObjects("Log").Range.AutoFilter Field:=3, Criteria1:=Status
    End If

 

[6467100000]

Thanks for your help on this, I am currently working on Excel 2003.

When i referred to drop down list what I meant was each of the cells has a data validation list attached to is and so I want the filer to filter the rows in sheet two based on the value of the cells in row 9 of sheet one.

The cells in sheet one relate to columns in sheet 2 and so the value of D9 in sheet 1 should be used to autofilter column K in sheet 2.

Hope this makes sense.