Conditional Formatting
Posted by Shailendra Sharma on January 22, 2002 2:17 PM
Is there a way to copy and paste the conditional formatting to different cells so that the cell reference used in the conditional formatting changes accordingly?
For example: Let's say that cell A9 contains the formula for average of 7 numbers above (A2:A8). Now, a number is inserted every day and at the end of the week, I'll get my average for that week. Until the range A2:A8 is null, cell A9 shows #DIV/0!. So, I insert the conditional formatting that if "Formula Is" "=iserror($A$9)" then format cell so that its fill color is white, which in turns hide the error "#DIV/0!". But when I try to copy and paste this formula in B9, C9, D9, etc., the reference in "=is error($A$9)" still remains as A9. It does not change to B9, C9, or D9, accordingly. So, what happens that as soon as I enter a value in the range A2:A8, the result of A9 is not an error anymore and other cells B9, C9, D9, etc. then display "#DIV/0!" again, in spite of the conditional formatting. So, in order to hide any formula result errors, I've to apply conditional formatting to each cell separately.
The same thing happens if I apply conditional formatting that if "Cell Value Is" "Equal To" "0", then format cell such that fill color is whilte. I've been doing this to display results of a formula only if there is a valid value and to hide any errors.
Posted by Steve Hartman on January 22, 2002 2:20 PM
Before pasting remove the $ signs in the conditional formating "Formula is" it will then copy the way you want it.
Posted by Mark W. on January 22, 2002 2:27 PM
> Is there a way to copy and paste the
> conditional formatting to different cells
> so that the cell reference used in the
> conditional formatting changes accordingly?
Yes, but it seems that you're using Conditional
Formatting to compensate for your average formula's
shortcomings. Instead, why don't you use...
=AVERAGE(IF(A2:A8,A2:A8))
...and, you won't get a #DIV/0! error.
