Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Conditional Formatting
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? |
#2
|
|||
|
|||
Hi
you can't reference cells in a different worksheet within conditional formatting. You have to use a defined name for this -- Regards Frank Kabel Frankfurt, Germany "Pat Z." <Pat schrieb im Newsbeitrag ... 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? |
#3
|
|||
|
|||
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 "Pat Z." wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
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 "Frank Kabel" wrote: Hi you can't reference cells in a different worksheet within conditional formatting. You have to use a defined name for this -- Regards Frank Kabel Frankfurt, Germany "Pat Z." <Pat schrieb im Newsbeitrag ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding zeros from range calculations
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 "Pat Z." wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Dependant Conditional Formatting | Excel Discussion (Misc queries) | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Conditional Formatting | Excel Worksheet Functions |