+ Reply to Thread
Results 1 to 12 of 12

Pass/fail result from data table

  1. #1
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Pass/fail result from data table

    I have attached the document i am working on. I will be entering a Ref and 2 results (A & B). I would then like it to be able to automatically reference the data on sheet 2 to see if it passes or fails. How this is best done I'm not sure. I had thought of a check box but this may not be the best method. Green or red colour would be great. May not need the pass and fail column?
    Any help would be fantastic as i have been sat staring for a long time with no real solution.
    Many thanks
    Attached Files Attached Files
    Last edited by ms85; 08-06-2015 at 10:59 AM.

  2. #2
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: What formula??

    HI..

    Use (VLOOKUP($H2,Sheet1!$B$2:$E$24,3,0)) for A & B results in next sheet and pls let me know wht is the criteria for pass & fail?? is there any number decided like for e.g."100",if exceeds, passes or fails??

    Please find the attached sheet for vlookup
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Re: What formula??

    Hi,
    Pass is anything between and including the lower & upper limits on both A & B which i will be inputting

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: What formula??

    You don't need BOTH columns for Pass and Fail, they're mutually exclusive.

    =IF(AND(D2>=VLOOKUP(B2,Sheet2!A$2:E$29,2,0),D2<=VLOOKUP(B2,Sheet2!A$2:E$29,3,0),E2>=VLOOKUP(C2,Sheet2!A$2:E$29,4,0),E2<=VLOOKUP(C2,Sheet2!A$2:E$29,5,0)),"Pass","Fail")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Re: What formula??

    Where would that formula go? I have placed it in the pass column and it states #N/A

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: What formula??

    Oops! My mistake, try this instead

    in Sheet1!F2
    =IF(AND(D2>=VLOOKUP(B2,Sheet2!A$2:E$29,2,0),D2<=VLOOKUP(B2,Sheet2!A$2:E$29,3,0),E2>=VLOOKUP(B2,Sheet2!A$2:E$29,4,0),E2<=VLOOKUP(B2,Sheet2!A$2:E$29,5,0)),"Pass","Fail")
    and copy down the column for each entry you have

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: What formula??

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

    Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

    To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (note: this change is not optional )
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Re: Pass/fail result from data table

    Many thanks that works great for 314 but i have tried a couple of others and it is stating fail when it should be pass. Is there something i have missed. I have attached the test sheet again
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Pass/fail result from data table

    Your values in Sheet2 for columns D and E are text not numbers

    Either change them to numbers
    or amend the formula to
    =IF(AND(D2>=VLOOKUP(B2,Sheet2!A$2:E$29,2,0)+0,D2<=VLOOKUP(B2,Sheet2!A$2:E$29,3,0)+0,E2>=VLOOKUP(B2,Sheet2!A$2:E$29,4,0)+0,E2<=VLOOKUP(B2,Sheet2!A$2:E$29,5,0)+0),"Pass","Fail"

  10. #10
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Re: Pass/fail result from data table

    That is brilliant, thank you. Adjusted them to numbers and the original formula worked.
    Is it at all possible to get the pass to be green and fail red. I could be asking the impossible but thought i would check

  11. #11
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Pass/fail result from data table

    Use Conditional Formatting

    Select F2

    Conditional Formatting
    New Rule
    Use a formula to determine...

    2 Rules required one for each colour.

    =(F2="Pass")
    Format as Green

    =(F2="Fail")
    Format as Red

    Use Format painter (paintbrush icon) to copy to other cells

  12. #12
    Registered User
    Join Date
    08-06-2015
    Location
    Gloucestershire, England
    MS-Off Ver
    2007
    Posts
    6

    Re: Pass/fail result from data table

    You are a star thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  2. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1