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]

Try this:



=IF(A1<>"test","result",ABS(C5))

 

[Joe4]

I think you are confusing a few different issues here. So let's state some points of clarification:
- Any cell can either have a hard-coded value, or a formula in it, but never both at the same time
- A formula can only return a value to the cell it is stored in (cannot return a value to some other cell)
- Conditional Formatting is only used to change the format of a cell (color, boldness, underlining, italics, format, etc)
- Conditional Formatting does not change/affect the values returned in a cell

So, can you explain to us again exactly what you want to happen?
Is it really Conditional Formatting? If so, what rule do you want, and what is the formatting that you want changed?

 

[dmills335]

thanks dante,

unfortunately, it still returns a negative value.

 

[Joe4]

unfortunately, it still returns a negative value.

I do not see how that is possible.
Is your value in C5 really numeric or is it text?

 

[DanteAmor]

I think he has something like this. Formula and CF in B3

With ABS function:

The word "result" appears without the sign -

 

[dmills335]

hi joe,

i am using the cell to carry out a vlookup to get it's value from another worksheet.
depending on the result of that value it will be coloured using conditional formatting.
i am also using conditional formatting to identify the result of a different cell and then output a text value using the format>menu>custom menu.

so,
vlookup provides "123" and cell "A1" provides "a" - no formatting of current cell required.
vlookup provides "-123" and cell "A1" provides "a" - format current cell with "-123" red.
vlookup provides "123" and cell "A1" provides "x" - format current cell with green fill and "X" text (regardless of vlookup, if cell "A1" = "x" then current cell = green fill and "X" text)

 

[DanteAmor]

thanks dante,

unfortunately, it still returns a negative value.

We are trying to guess.
You could clearly explain what you have and exactly where you have it.
If you can upload an image to clarify it, it would be better.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[dmills335]

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.

 

[DanteAmor]

hi joe,

i am using the cell to carry out a vlookup to get it's value from another worksheet.
depending on the result of that value it will be coloured using conditional formatting.
i am also using conditional formatting to identify the result of a different cell and then output a text value using the format>menu>custom menu.

so,
vlookup provides "123" and cell "A1" provides "a" - no formatting of current cell required.
vlookup provides "-123" and cell "A1" provides "a" - format current cell with "-123" red.
vlookup provides "123" and cell "A1" provides "x" - format current cell with green fill and "X" text (regardless of vlookup, if cell "A1" = "x" then current cell = green fill and "X" text)

In which cell do you have this:

=IF(A1<>"test","result",C5)

In which cell do you have the vlookup?

What do you have in A1, formula or CF?