+ Reply to Thread
Results 1 to 12 of 12

Change Cell Value Based on Data in Range of Cells

  1. #1
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Change Cell Value Based on Data in Range of Cells

    I have a cell A1 that I want to show as "Compliant" or "Not Compliant." This cell is based on columns O and P. A1 should be "Compliant" if all of the values in column O are less than the corresponding values in column P (ie. O1<P1, O2<P2, O3<P3, etc.). If there is any case where a value in column O is greater than the value in column P, then A1 should be "Not Complaint."

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,176

    Re: Change Cell Value Based on Data in Range of Cells

    Insert a help column that count the instance when O is greater than P. If the instance is greater than 0 then result will be Not Compliant.
    Attached Files Attached Files

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Change Cell Value Based on Data in Range of Cells

    Hi

    If i get your point, then,Try.

    =IF(O1<P1;"Complaint"."Not Complaint")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Change Cell Value Based on Data in Range of Cells

    You can do this with a user defined function

    Please Login or Register  to view this content.
    Open the VBA editor (Alt F11). Insert a new module (Insert - Module) and paste in the above. Alt F11 back to worksheet.

    in A1 type

    =CheckCompliance(O1:O22,P1:P22)

    adjusted for your actual range.

    Note that you didn't specify what should happen if the values in O and P were the same. I've assumed this means non-compliant. If this is not the case, change the >= into just >.
    Martin

  5. #5
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Re: Change Cell Value Based on Data in Range of Cells

    Thanks. Is there a way to do this without VBA? I was hoping there was a formula I could use in cell A1.

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Change Cell Value Based on Data in Range of Cells

    Try this array formula (entered with Ctrl+Shift+Enter)
    =IF(PRODUCT(IF(O1:O11<P1:P11,1,0)),"","Not ")&"Compliant"

    Adjust the ranges to suit

    Note that you haven't allowed for the situation where a value in O equals it's adjacent value in P

  7. #7
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Re: Change Cell Value Based on Data in Range of Cells

    That's perfect. Thanks, Cutter! I made sure to change the formula to <= when O equals P.

  8. #8
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Re: Change Cell Value Based on Data in Range of Cells

    One more question, Cutter. Any advice on how to make the formula work such that the formula only accounts for values in columns O and P, when the corresponding value in column D = "adhesive"? Meaning, A1 should say "Compliant" if D4="adhesive" and O4<=P4 AND D5="other" and O5>P5.

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Change Cell Value Based on Data in Range of Cells

    Sorry, I don't get what you're trying to achieve. You're asking for a result based on values in one row compared to values in the row below?

  10. #10
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Re: Change Cell Value Based on Data in Range of Cells

    A1 indicates "compliant" or "not compiant". In rows 5-30, it first looks at column D to find "adhesive". When cell D# = "adhesive," it then checks to see if cell O#<=P#. (# represents any number between 5-30.)

    So, when looking at rows 5-30, if the following conditions are true, A1 is "compliant":
    D7 = adhesive, O7<=P7
    D8 = adhesive, O8<=P8
    D22 = adhesive, O22<=P22
    D25 = paint, O25>P25 (even though O25>P25, it's doesn't affect compliance because it's a paint instead of an adhesive)

    Hope that's a better explanation

  11. #11
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Change Cell Value Based on Data in Range of Cells

    How about:

    =IF(SUMPRODUCT((D5:D30="adhesive")*(O5:O30>P5:P30)),"Not ","")&"Compliant"

  12. #12
    Registered User
    Join Date
    10-21-2005
    Posts
    18

    Re: Change Cell Value Based on Data in Range of Cells

    PERFECT! Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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