Help whit Advanced Filter

[Mauricio Samy Silva]

Hi,
Assuming range A20:A100 to be filtered;
Range A1:A4 has restrictions for filtering;
A1 and A20 are label cells of course
I want hidde cells whit 0 (zero), MALE and CLUB, and displays all others.
What must I type in A2=> (>0)???, A3=>(>MALE)???, A4 (>CLUB)???. I do it but don't work, it hidde only zeros (hi). Help, how do!

 

[Aladin Akyurek]

On 2002-08-24 14:30, Mauricio Samy Silva wrote:
Hi,
Assuming range A20:A100 to be filtered;
Range A1:A4 has restrictions for filtering;
A1 and A20 are label cells of course
I want hidde cells whit 0 (zero), MALE and CLUB, and displays all others.
What must I type in A2=> (>0)???, A3=>(>MALE)???, A4 (>CLUB)???. I do it but don't work, it hidde only zeros (hi). Help, how do!

Am I understanding correctly that you want filter A2:A100 in sheet X to a sheet Y by applying the conditions/criteria that a filtered value must not be MALE or CLUB or 0?

 

[Mauricio Samy Silva]

Thanks Aladin!
More simple! Only want aplly a local filter.
Filtered at current sheet.

 

[Aladin Akyurek]

Mauricio,

Advanced Filter can be at times unnerving...

Let A1:A15 house the following sample:

{"Items";1;0;3;"CLUB";6;"Brasil";0.6;"College";10;0;"MALE";"Nadia";6;5}

where "Items" is a label.

( 1.) Format the label in a distinct manner, e.g., in bold and italic.

( 2.) In C1 enter: New

which is a label.

( 3.) In C2 enter:

=NOT(OR(A2={"CLUB","MALE",0}))

which constitutes a computed criteria, saying that the the entry filtered must not equal either CLUB or MALE or 0 (zero).

( 4.) Click in A2.

( 5.) Activate Data|Filter|Advanced Filter.

( 6.) Check Copy to another location.

( 7.) Enter $A$1:$A$15 (if not already there) in the box for List range.

( 8.) Enter $C$1:$C$2 in the box for Criteria range.

( 9.) Enter, say, $E$1 in the box for Copy to.

(10.) Leave Unique records only unchecked.

(11.) click OK.


See the figure...

aaAdvFilter Mauricio.xls
	A	B	C	D	E
1	Items		New		Items
2	1		TRUE		1
3	0				3
4	3				6
5	CLUB				Brasil
6	6				0.6
7	Brasil				College
8	0.6				10
9	College				Nadia
10	10				6
11	0				5
12	MALE
13	Nadia
14	6
15	5
Sheet1

Aladin

 

[Mauricio Samy Silva]

Great Aladin!
Thanks a million
A very interest and intelligent way to set criteria in advanced filter, not found in "search tools", so clear as you explained.
I'll use it and credit to you of course!

_________________
Thanks,
Mauricio Samy Silva
Windows-98/Excel-2000
I love this Site
This message was edited by Mauricio Samy Silva on 2002-08-24 16:21