Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    If Problem (Excel 2000)

    Hi

    I am trying to say here if anywhere within the range P7:P90 in worksheet Lead Range contains the text "Too Low","Needs Check","OK"


    Too Low may appear more than once, Should only be OK if it does not appear at all


    =IF('Lead Range'!P7:P90="Too Low","Needs Check","OK")


    Many thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: If Problem (Excel 2000)

    Try this:

    =IF(ISNA(MATCH("Too Low",P7:P90,0)),"OK","Needs Check")

    The MATCH function tries to find the text "Too Low" in the range P7:P90. If not found, it returns #N/A, so we use ISNA to test for this.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Problem (Excel 2000)

    Hi Hans

    I can't get it to work, does it matter that the text Too Low is created by a formula ie =IF(ISBLANK(Q7),"",IF(Q7<O7,"Too Low",IF((AND(Q7>=O7,Q7<M7)),"Needs Approval","OK")))

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Problem (Excel 2000)

    Hi Hans

    I think the problem is the formula does not contain the sheet ref Lead Range this is where the range is situated,

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: If Problem (Excel 2000)

    It should work, but I forgot to specify the worksheet:

    =IF(ISNA(MATCH("Too Low",'Lead Range'!P7:P90,0)),"OK","Needs Check")

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: If Problem (Excel 2000)

    Hi Hans

    Works fine now, thanks again.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

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