conditional formatting with an if statement

[dmills335]

hello,

i am trying to use conditional formatting to format a cell with text based on the value of another cell.
for example, if cell "A1" = "test" then cell "C5" = "result"

i am using the custom format to output "result" and a bodged together if statement to carry out the function =IF(A1<>"test","result",C5).
however, "result" overwrites the current value of a numeric cell with text; if that numeric cell has a negative number, "result" displays the text with a preceding "-".

any help with a more elegant if statement and the formatting of the negative text would be greatly appreciated.

 

[DanteAmor]

hi dante,

i have =IF(A1<>"test","result",C5) as a formula in the conditional formatting.
this works other than the negative value for a number provides a negative sign before the text.

You can put an image to see how you put that formula in the conditional format.
What version of excel do you have?
The CF only validates if the formula is true, you can not put the IF statement (condition, true, false), or did I miss something?

 

[Joe4]

When writing a Conditional Formatting formula, you typically do not use an IF statement, because you are not returning a value. Conditional Formatting is simply looking for a formula to return TRUE or FALSE. If it is TRUE, it will apply the selected Conditional Formatting.

vlookup provides "-123" and cell "A1" provides "a" - format current cell with "-123" red.

So if the VLOOKUP is in cell C5, and you want to check for a negative, and if you want to see if "a" appears in cell A1, then your Conditional Formatting formula would look like:

=AND(C5<0,A1="a")

The "AND" function returns TRUE if all listed conditions are True, otherwise it returns False.

 

[dmills335]

please see the links below.
the vlookup refers to an external sheet.
the conditional formatting applies to this sheet.
the vlookup and the conditional formatting are both applied to the cell highlighted with the red ring.

https://www.dropbox.com/s/8vpzc2butdmiot7/Excel1.png?dl=0
https://www.dropbox.com/s/a4qlhp12f9h9t5v/Excel2.png?dl=0
https://www.dropbox.com/s/6mlgq4usvod0a8v/Excel3.png?dl=0

thanks.

 

[dmills335]

this link show the conditional formatting.

https://www.dropbox.com/s/ak3f2e35y3px2tn/Excel4.png?dl=0

 

[Joe4]

Did you read my last reply, regarding the issues with how you constructed your CF formulas?

 

[DanteAmor]

Change your formula on the sheet by this

=ABS(VLOOKUP("350118",'Mileage data'!C1:H100,5,FALSE))

-----
And the formula in the conditional format is not correct, from my point of view, but if it works for you, then so stay.

 

[dmills335]

unfortunately, altering the vlookup to provide an absolute value means that the result will always be positive.
i still require negative data, i just don't want the conditional formatting to provide the text with a negative sign; that's why i've put the IF statement within the conditional formatting formula.

 

[Joe4]

Is your VLOOKKUP actually returning a numeric value or a text one?
You can easily verify with the ISNUMBER function.
So, if the value is being returned to cell C5, what does this formula return (put in any blank cell)?
=ISNUMBER(C5)

If it returns FALSE, then it is returning a Text entry, not a Numeric one, and applying any sort of Custom Formatting to the value would only apply to Numeric values.

that's why i've put the IF statement within the conditional formatting formula.

As we stated before, Conditional Formatting does not (and can not) change value being returned in the cell, only its formatting.
Your Conditional Formatting formula should only return TRUE or FALSE, and if it is TRUE, it will apply whatever formatting features you have selected.

 

[DanteAmor]

unfortunately, altering the vlookup to provide an absolute value means that the result will always be positive.
i still require negative data, i just don't want the conditional formatting to provide the text with a negative sign; that's why i've put the IF statement within the conditional formatting formula.

Okay, then leave your formula

---

Change

Type:
"E"

By:
"E";"E"

 

[dmills335]

hi joe,
i agree with you regarding the true / false return and the conditional formatting.
the vlookup is returning a NUMBER, the conditional formatting i am using forces the cell to return a TEXT value (https://www.dropbox.com/s/ak3f2e35y3px2tn/Excel4.png?dl=0).

as i stated before, the formatting i am using works for everything except where i have a negative NUMBER value.
i get a negative, "-", symbol before the conditionally formatted TEXT output.
this is the crux of what i am trying to get around.

the ABS solution would work nicely if i didn't require the negative data as well as positive, but unfortunately i do.