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

    Help with Formula (Excel 2002/2003)

    Hi

    =IF(COUNTBLANK('Lead Range'!Q7:Q97,=0,"No Data"),IF(ISNA(MATCH("Too Low",'Lead Range'!Q7:Q97,0)),"At least one Lead Price is Too Low")"Lead Prices OK")


    A little help needed please, what I am trying to say here is if the range 'Lead Range'!Q7:Q97=0 enter the text "No Data" if "Too Low is matched the enter the text "At least one Lead Price is Too Low" else 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: Help with Formula (Excel 2002/2003)

    Please explain precisely what you mean by "if the range 'Lead Range'!Q7:Q97=0". Do you want to test if all cells are 0? Or blank? Or ...?

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

    Re: Help with Formula (Excel 2002/2003)

    Hi Hans

    The range Q7:Q97 is in a worksheet called Lead range and it contains formulas based on the Column R, if for instance 15.00 is entered in R7 Q7 will either contain the text "Too Low or "OK" that is where the match comes in How ever when the workbook is first opened there will be no data in Column R, so potentially the range Q7:Q9 wil contain nothing but the formula, I want to put my formula in the opening worksheet which will display "No Data" because no one has yet entered any data in Column R of the worksheet Lead Data.

    Hope this is clearer

    Many thanks

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

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

    Re: Help with Formula (Excel 2002/2003)

    Where does the 0 come into it? What is the result of the formulas in column Q if nothing has been entered yet in column R? Zero, or an empty string "", or something else? Please try to give a precise answer.

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

    Re: Help with Formula (Excel 2002/2003)

    Hi Hans

    I not sure what you mean by where does the 0 come into it, If nothing is entered into ColumnR the cells in Column Q appear to be blank, I presume this is what you mean by a blank string.

    Many Thanks

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

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

    Re: Help with Formula (Excel 2002/2003)

    Your original question stated "if the range 'Lead Range'!Q7:Q97=0". I was trying to find out what you meant by 0. Anyway, try this:

    =IF(COUNTIF('Lead Range'!R7:R97,"<>")=0,"No Data",IF(ISNA(MATCH("Too Low",'Lead Range'!Q7:Q97,0)),"Lead Prices OK","At least one Lead Price is Too Low"))

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

    Re: Help with Formula (Excel 2002/2003)

    Hi Hans

    I now understand what you said about the 0 I was trying to use it with Countblank.

    Once again I am deeply in your debt, as the formula you supplied does exactly what I need. <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    Many Thanks

    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
  •