12 Replies Latest reply on Mar 22, 2017 12:14 AM by bigtom

# Conditional Formatting???

Ok I get the basics of conditional formatting, i.e. if the value is greater than 50 then turn the text red. What I am trying to find is - Is it possible to have the condition look at the value of another field? I.e. if field B is 5 greater than field A then turn the text red?

• ###### 1. Re: Conditional Formatting???

it would be something like (in Field "A" conditional formula):

FieldB - 5 > FieldA

beverly

• ###### 2. Re: Conditional Formatting???

To echo what beverly said, yes. The conditional formattting can be just about any calculation that will return  true/false.

• ###### 3. Re: Conditional Formatting???

I have a table that tracks COGS as a percentage of Retail Price I use it to estimate pricing. I have an Ideal COGS Percentage which for most items is 45% and Actual COGS Percentage based on Material and Labor Divided by Retail Price. I would like for the Actual COGS Percentage to change to red text if it exceeds the ideal percentage by 5 and to change to green if it is more then 5 lower than the Ideal so two conditions.

If I am understanding what you are saying then I would put into the conditional formatting ActualCOGS -5 > IdealCOGS Does this just go into the conditional formatting dropdown?

• ###### 4. Re: Conditional Formatting???

click on field in layout mode and select "Conditional Formatting" from the contextual menu or "Conditional" from the "Format" menu.

you set the "Condition" to Formula.

• ###### 5. Re: Conditional Formatting???

You would do 2 conditional formatting steps on the same item.  Use the formula is - and click on specify.  You can use Self to represent the current field that you are applying the formatting to.  We use Self<.1 to flag margins less than 10%

The specifics of whether its 5 or .05 would depend on whether or not you are storing 45 as ideal or .45

Format Red

Self > IdealCOGS + 5

Format Green

Self < IdealCOGS - 5

1 of 1 people found this helpful
• ###### 6. Re: Conditional Formatting???

Thank you I found where to put a formula in. But it does not seem to matter how I write this formula it does not change the text color???

• ###### 7. Re: Conditional Formatting???

what formula are you using? (post here)

• ###### 8. Re: Conditional Formatting???

Well it gives me this - Self≥"" and Self≤""

I have been trying all kinds of variations of

SIPMA Order Form Price List::COGS Actual Percentage - 5  > SIPMA Order Form Price List::COGS Ideal Percentage

And

Self - 5  > SIPMA Order Form Price List::COGS Ideal Percentage

It does not matter if i put the fields in () or "" it acts like it takes the formula but nothing happens with the associated text.

• ###### 9. Re: Conditional Formatting???

Here's what we use

Make sure you use the "formula is" and then hit specify to type it in.  You don't need to use the default formatting or text they put in there.

1 of 1 people found this helpful
• ###### 10. Re: Conditional Formatting???

Depending on how your field is setup are you sure you should be using "5" and not "0.05"?

1 of 1 people found this helpful
• ###### 11. Re: Conditional Formatting???

Thanks everyone - I finally have this working.

• ###### 12. Re: Conditional Formatting???

Nice. Hopefully you can get your COGS down to 10%.