Advanced Filter Criteria (03)

[jstevens]

Is it possible to state a criteria in one cell? If I recall correctly, one was able to do this in Lotus.

Example:

Account
>=2000#or#2000e

Thanks,
John

[HansV]

You can create an 'or' condition by putting the conditions below each other:

<table border=1><td>Account</td><td>>=2000</td><td><=1500</td></table>
(your example is unclear, so I modified it). Or you can use a Criteria range like this (the second cell here shows the formula instead of the result):

<table border=1><td>Dummy</td><td>=OR(A2>=2000,A2<=1500)</td></table>
Replace A2 with the first data cell in the Account column.

[jstevens]

Hans,

The criteria is suppose to extract those accounts whose account value is >= to 2000 and where the account is "2000e" ie not numeric.

Thanks,
John

[HansV]

Do you really mean "and"? Or do you mean "or"? "And" doesn't seem very useful here.

[jstevens]

Hans,

Let's try "OR".

John

[HansV]

You can use the following as Criteria range:

<table border=1><td>Account</td><td>>=2000</td><td>2000e</td></table>
or

<table border=1><td>Dummy</td><td>=OR(A2>=2000,A2="2000e")</td></table>
where A2 is the first data cell in the Account column, and where the second cell in the criteria range contains the formula shown here (it'll display either TRUE or FALSE)

[jstevens]

Hans,

I tried it without success. I have attached a sample file.

Thanks,
John

[HansV]

As indicated implicitly in both my previous replies, you should use a dummy field name if you want to use a formula as criteria, not the actual field name. Enter Dummy or any other term that is not a valid field name in D1, or leave D1 blank.

[jstevens]

Hans,

What a day! Do I feel embarrased. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Thanks,
John

[HansV]

Don't worry, I should have stated it explicitly.