Conditional Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have cells with conditional formatting where condition is 'Cell Value is
greater than or equal to 180, format cell as green."
These same cells are also pointing to another worksheet in the workbook.
Some of the referenced cells are blank. These blanks result in a 0 (zero) in
the overall worksheet. If I enter a formula of
=IF(ISBLANK('11-29'!J5),"",'11-29'!J5) and copy the formula down the column,
my conditional formatting is not working properly. The formula itself is
working great, but I cannot have 0's in the sheet as the numbers are used in
an average formula.
Any ideas?
 
Hi
you can't reference cells in a different worksheet within conditional
formatting. You have to use a defined name for this
 
Use formula is instead of cell value in conditional formatting

=AND(cell_ref>=180,cell_ref<>"")

replace cell_ref with the cell(s) you are formatting

the reason is that "" is text and text is seen as greater than any number thus
greater than or equal to 180

Regards,

Peo Sjoblom
 
Can you reference a value in a different cell withing the same worksheet?

For Example if Cell E18 has a value of 1 and I want Cell H18 to have a
backround color of yellow when this cell has a value in it.

Doug
 
Hi Pat

Re your apparent problem with zeros in ranges that you need the averages for
(where I assume you don't want to have the zero values entering the maths).
There is a very good outcome that I use a lot. You need to change your normal
averaging formula (eg =Average(A1:A10)) to an array formula, in this
hypothetical case like this: =Average(if(A1:A10<>0;A1:A10;"")). Remember to
"enter" an array formula with cntrl-shft-enter.

Elardus
 
Back
Top