# Change Cell Value Based on Data in Range of Cells

1. ## 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. ## 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.

3. ## 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")

4. ## 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)

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 >.

5. ## 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. ## 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"

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

7. ## 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. ## 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. ## 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. ## 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":
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

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

PERFECT! Thanks!!

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

#### 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