Possible to use Conditional Formatting to change contents and not only the format of the cell?

1
votes

I would like to apply the following rule using Conditional Formatting:

=IF($A1>1;NA();$A1)

Whenever a cell is greater than 100% the value should change to #NA.

Applying the rule in:

Conditional Formatting => New Rule... => Use a formula to determine which cells to format => Format values where this formula is true:

does not produce the desired output.

Is it possible to achieve the desired output using Conditional Formatting in some other way?

Using the formula in another column and referencing it is not an option.

1
excelconditional-formatting
Do you want to change the actual raw value or just what you see displayed on the worksheet? - user4039065
No, you cannot change the cell contents using CF. - Rory
@Jeeped, is it possible to achieve one or the other? - Saud
Yes, the second is possible by using a CFR to change to a custom number format of \#\N\/\A when =$a1>1 - user4039065

1 Answers

4
votes

Changing what you see on the worksheet is possible by using a CFR to change to a custom number format of \#\N\/\A when =$a1>1. This does not change the actual cell value; only what you see.

enter image description here