Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using Excel 2007 and I need to compare some values.

    If "My Code"=L110 AND "Client Code" falls in the range Z004-Z009 THEN it's valid. If it falls outside the range, then it's an error.

    My Code..........Client Code
    L110................Z004
    L110................Z005
    L110................Z062

    How would I write this formula?

    Thank you for your help!
    Thanks,
    Caroline in lala-land

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - What should happen if "My Code" does not equal L110 ?

    If value of "My Code" does not matter, try the following formula in Column C to test for "Client Code" that falls in the range Z004-Z009.

    =IF(AND(B3>="Z004",B3<="Z009"),"Valid","Error")

    Assumes "My Code" is in Column A and "Client Code" is in Column B

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Tim for your response. So, the formula gets more complicated if My Code doesn't equal L110. I actually have several My Codes and Client Codes. My Code may be L110, L120, L130...L250 and there are ranges for each Client Code that should match with the corresponding My Code.

    For example:

    My Code..........Client Code Range
    L110................Z004-Z009
    L120................Z010-Z012
    L130................Z013-Z015

    What I'm trying to accomplish is some way to draw my attention to My Code so that if the Client Code is outside the range, I can see that there's an error and correct it.

    I hope this makes sense.

    Thank you again.
    Thanks,
    Caroline in lala-land

  4. #4
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - The attached workbook should do what is needed.

    There are two tabs:
    Data - Would be all of your data as it is currently laid out (I improvised and added Column C).
    Valid Codes - Would contain all of the valid code combinations in Columns B and C. Please note the formula in Column A.

    Range Name of "Codes" includes all of the Valid Codes info.

    The results on the Data tab gives #NA for all invalid code combinations.

    Hope this is what you need.

    Tim
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - You could also have the invalid cells show in color by using Conditional Formatting. This will automatically highlight cells with errors.
    See attached.

    Tim
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    California
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Tim:

    Your formulas worked perfectly. Thank you so much!

    Caroline
    Thanks,
    Caroline in lala-land

Posting Permissions

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