CODEWITHSUNDEEP
excelconditional-formatting
Saud
Saud

Reputation: 490

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

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.

Upvotes: 1

Views: 663

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 4

Related Questions