Filter a table using information in cells outside of the table

Question

I have a table that pulls in information from multiple different sources throughout my workbook. There are 4 categories that the table will be filtered on regularly and used by users that are not strong in excel.

I would like to set up a filtering section with lists that filter the table below. The screenshot shows the proposed setup with list values being in cells B4,C4,D4,E4 and the table below being what would be filtered based on the values that are input.

Does anyone know how to accomplish this or something similar?

^{(Click image to enlarge)}

Answer 1

You mean that you want to filter by a specific cell value, rather than filtering with the built in table filtering?

Well, you can call on the autofilter with a macro, and use the value of the cells as criterias.

You can put the macro in the sheet, and have it called automatically each time one of the relevant cells are changed with a Worksheet_Change sub.

There are a lot of ways to write this, but we need to specify what table we want to filter, and we also need to specify which row to filter.

This is the code i put in the sheet (by right clicking the sheet tab and select "show code") for my example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

Result:

Edit
Added loop to be able to clear all filters at once.

This Code requires your Headers to be the same over the input cell and in the Table, or it will throw an error.

-

Worksheet_Change is called when cells on the worksheet are changed.
This is often used in conjunction with
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
to restrict the sub from running unless specific areas are targeted.

Having a restriction on Target.Count is also a good idea, to keep the code from crashing. Often you want to restrict it to a single target, unless you intend to loop the selection, like I did in this case.

If you like to have drop down lists with the options, there's a a VBA approach here: excel-removing-duplicates-with-data-validation

Answer 2

I would like to suggest an Array (CSE) Formula, will help you to extract multiple Rows based on few Criteria.

How it works:

• I'm assuming that the Source Data is in Range A2:E10.
• Criteria Range is A16:E16.
• Enter this Formula in Cell A20, finish with Ctrl+Shift+Enter, fill Right then Down.

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

Note:

• If Criteria Cell/Cells will blank, this Formula will return All records.

• You may put one ore more than one Criteria to Filter related Records.

Let me explain the mechanism of the Command.

• MMULT function can't work with Boolean values so in order to get that working, Formula must multiply the array with 1.

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

becomes,

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

and returns,

{0;0;1;0;0;2;1;1;2;1}

and,

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

becomes,

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

becomes,

{0;0;1;0;0;2;1;1;2;1}=2

and returns,

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

becomes,

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

• SMALL Function gets the k-th smallest number in an Array.

• INDEX Function returns a value from a cell Range or Array, based on a Row and Column number.