Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced Filter Criteria (03)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filter Criteria (03)

    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.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter Criteria (03)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filter Criteria (03)

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

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter Criteria (03)

    Hans,

    Let's try "OR".

    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filter Criteria (03)

    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)

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter Criteria (03)

    Hans,

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

    Thanks,
    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filter Criteria (03)

    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.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced Filter Criteria (03)

    Hans,

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

    Thanks,
    John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced Filter Criteria (03)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •