Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced filter criteria (Excel 2000)

    Having difficulty with setting an advanced filter. My columns are account,asset, beginning, additions, retirements, transfers, ending. What I am attemtping to filter is assets in a certian number range which have additions and retirement amounts equal. My asset number criteira in row one are asset ">=x" and the row two underneath "<=y" for the asset number range, and, without the column label in the criteria range retirement column, =d2=e2 (addtions = retirements). Yet when I run the filter, I get ALL the assets in the range, including those with no additons and retirement activity. The "answer" is further down the output range. Why am I getting these extra records, and not just those where additions = retirements. I just realized additions=retirements when they're blank, too. That is probably it. But then how do I exclude blanks from the output? Putting a >0 beneath =d2=e2 did not exclude the blank fields. Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Advanced filter criteria (Excel 2000)

    Put =d2=e2 in the 2nd row also.

    Criteria in the same row are all ANDed. Criteria in different rows are ORed.

    Your are currently looking for:
    (>=x AND d2=e2 ) OR ("<=y")

    and (I think you would want
    (>=x AND d2=e2 ) OR ("<=y" AND d2=e2)

    You can use =not(isblank(d2)) to not have blanks in D2

    Steve

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced filter criteria (Excel 2000)

    Thanks. I setup =AND(NOT(ISBLANK(F2)),F2=H2)and that seems to do the trick.

Posting Permissions

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