#### Mauricio Samy Silva

##### New Member
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!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

##### MrExcel MVP
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

##### New Member
More simple! Only want aplly a local filter.
Filtered at current sheet.

##### MrExcel MVP
Mauricio,

Advanced Filter can be at times unnerving...

Let A1:A15 house the following sample:

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.

( 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...
ABCDE
1ItemsNewItems
21TRUE1
303
436
5CLUBBrasil
660.6
7BrasilCollege
80.610
10106
1105
12MALE
146
155
Sheet1

#### Mauricio Samy Silva

##### New Member
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

Replies
2
Views
182
Replies
8
Views
193
Replies
4
Views
199
Replies
1
Views
250
Replies
1
Views
106

1,196,017
Messages
6,012,872
Members
441,737
Latest member
bijayche

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back