filters

[jaybee7171]

is there a way to set up a filter, so that it automatically "refreshes" itself, when the members of the list that the filter is on, changes. for example, I am looking up data from a source, then filtering it on the destination sheet. when the source changes, the filter doesn't not adapt. Any help?

 

[Yogi Anand]

is there a way to set up a filter, so that it automatically "refreshes" itself, when the members of the list that the filter is on, changes. for example, I am looking up data from a source, then filtering it on the destination sheet. when the source changes, the filter doesn't not adapt. Any help?

Hi jaybee:

Welcome to MrExcel Board!

I have used the following code ...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If [E3] = 0 Then GoTo finish
    With Range("A5:a19")
    .AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "e1:e2"), CopyToRange:=Range("E5"), Unique:=False
    End With
    Range("a6:a65536").Copy Range("c6")
finish:
End Sub

with a simple dataSet as in the following illustration ...

y051118h2.xls
	A	B	C	D	E	F
1		dataSet			New
2		criterion			a_1
3		copy_to			0
4			DataSet		extracted
5	New		Old		New
6	a_1		a_1		a_1
7	a_2		a_2		a_16
8	a_3		a_3		a_17
9	a_4		a_4		a_10
10	a_5		a_5		a_11
11	a_16		a_16		a_12
12	a_17		a_17		a_13
13	a_8		a_8		a_14
14	a_9		a_9
15	a_10		a_10
16	a_11		a_11
17	a_12		a_12
18	a_13		a_13
19	a_14		a_14
20
Sheet1

Is this something like what you are looking for?