Advanced filter and UDF as input?

[Cedric S]

Hi
I have a table and I want to use the Advanced Filter to copy to another sheet

This table contains for example<TABLE> <TR> <TD>Column A</TD> <TD>Column B</TD> <TD>Column C</TD> <TD>Column D</TD> </TR> <TR> <TD>Status</TD> <TD>User</TD> <TD>Division</TD> <TD>Call Number</TD> </TR><TR><TD>Open</TD><TD>Peter</TD><TD>13</TD><TD>1</TD></TR><TR><TD>Open</TD><TD>Peter</TD><TD>15</TD><TD>2</TD></TR><TR><TD>Open</TD><TD>Ray</TD><TD>13</TD><TD>3</TD></TR><TR><TD>Closed</TD><TD>Ray</TD><TD>13</TD><TD>4</TD></TR><TR><TD>Wait</TD><TD>Jane</TD><TD>10</TD><TD>5</TD></TR>
<TR><TD>Open</TD><TD>Jane</TD><TD>11</TD><TD>6</TD></TR>

</TABLE>

I want to apply a criteria range for this table as followed:
<TABLE>
<TR> <TD>Status</TD> <TD>User</TD> <TD>Division</TD> <TD>Call Number</TD> </TR>
<TR><TD>Open or wait</TD><TD>Peter or jane</TD><TD>10 or 11 or 13</TD><TD></TD></TR></TABLE>

Is it possible to define such kind of criteria without having x times x unique rows?
I was thinking about an UDF where for example you could say
<TABLE>
<TR> <TD>Status</TD> <TD>User</TD> <TD>Division</TD> <TD>Call Number</TD> </TR>
<TR><TD>{=IsMember(SelectedFilter!A:A)}</TD><TD>{=IsMember(SelectedFilter!B:B)}</TD><TD>{=IsMember(SelectedFilter!C:C)}</TD><TD></TD></TR></TABLE>

this is the result of my SelectedFilter sheet:
<TABLE>
<TR> <TD>Status</TD> <TD>User</TD> <TD>Division</TD> <TD>Call Number</TD> </TR>
<TR><TD>Open</TD><TD>Peter</TD><TD>10</TD><TD></TD></TR>
<TR><TD>Wait</TD><TD>Jane</TD><TD>11</TD><TD></TD></TR>
<TR><TD></TD><TD></TD><TD>13</TD><TD></TD></TR>
</TABLE>

Please note that if you run the above report you would get something different as then it doesnt show open & jane (record no 6)

Somebody who could help me?
Can somebody help me starting with this UDF formula maybe, as i have no idea how to compare the row x (from advanced filter) with my Column A:A (loop over A1, A2,A3, ...) as I would not have an idea what value is in row x
Thanks

Cédric

 

[Cedric S]

Please note that in my real project there could be up to 100 user names and more than 8 search criteria which should be combined with an AND between search criteria type but an OR in search criteria values