Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Does the subject even make sense?
I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Depending on what you used for your conditional formatting rules, maybe you
could just add another requirement: =and(counta(c12:g12)=5,(yourexistingrules)) "Pheasant Plucker®" wrote: Does the subject even make sense? I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Hi Dave,
Thanks for your quick reply. The existing formula in H20 is [=H17-H19] Conditional Formatting for H20 is two-fold; Cell Value between 1 - 79.99 Format pattern Red Cell Value greater than or equal to Format 80 pattern Green I did try what you suggested by prefixing the existing formula to give; [=AND(COUNTA(C12:G12)=5,H17-H19)) but then the word FALSE or TRUE appears in cell H20 which I do not want. Nice try - thanks anyway :-) Kind regards, -=pp=- "Dave Peterson" wrote in message ... Depending on what you used for your conditional formatting rules, maybe you could just add another requirement: =and(counta(c12:g12)=5,(yourexistingrules)) "Pheasant Plucker®" wrote: Does the subject even make sense? I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Hi!
Try formatting the text the same colour as the background Alf |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
I didn't mean for you to change the formula in the cell. I meant for you to
change the conditional formatting formula/rules. Since you're using "Cell value is", you'll have to change it to "Formula is" First format: =and(counta(c12:g12)=5,and(1<=h20,h20<80)) And second format: =and(counta(c12:g12)=5,h20=80) "Pheasant Plucker®" wrote: Hi Dave, Thanks for your quick reply. The existing formula in H20 is [=H17-H19] Conditional Formatting for H20 is two-fold; Cell Value between 1 - 79.99 Format pattern Red Cell Value greater than or equal to Format 80 pattern Green I did try what you suggested by prefixing the existing formula to give; [=AND(COUNTA(C12:G12)=5,H17-H19)) but then the word FALSE or TRUE appears in cell H20 which I do not want. Nice try - thanks anyway :-) Kind regards, -=pp=- "Dave Peterson" wrote in message ... Depending on what you used for your conditional formatting rules, maybe you could just add another requirement: =and(counta(c12:g12)=5,(yourexistingrules)) "Pheasant Plucker®" wrote: Does the subject even make sense? I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Whoops...sorry Dave.
Shows my ignorance I guess..:-/ I did try the CF that you suggested and the cell doesn't turn red immediately when you input the first number in the first cell which was what I wanted. However it stays yellow all the time now! It doesn't turn green when above 80 nor red below 80 even after those 5 cells have data in - it just stays yellow... No worries...probably I missed something else fundamental... Thanks for trying. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I didn't mean for you to change the formula in the cell. I meant for you to change the conditional formatting formula/rules. Since you're using "Cell value is", you'll have to change it to "Formula is" First format: =and(counta(c12:g12)=5,and(1<=h20,h20<80)) And second format: =and(counta(c12:g12)=5,h20=80) "Pheasant Plucker®" wrote: Hi Dave, Thanks for your quick reply. The existing formula in H20 is [=H17-H19] Conditional Formatting for H20 is two-fold; Cell Value between 1 - 79.99 Format pattern Red Cell Value greater than or equal to Format 80 pattern Green I did try what you suggested by prefixing the existing formula to give; [=AND(COUNTA(C12:G12)=5,H17-H19)) but then the word FALSE or TRUE appears in cell H20 which I do not want. Nice try - thanks anyway :-) Kind regards, -=pp=- "Dave Peterson" wrote in message ... Depending on what you used for your conditional formatting rules, maybe you could just add another requirement: =and(counta(c12:g12)=5,(yourexistingrules)) "Pheasant Plucker®" wrote: Does the subject even make sense? I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Maybe you could post exactly what you did.
select your cell and format|Conditional formatting Then copy both formulas into your reply. "Pheasant Plucker®" wrote: Whoops...sorry Dave. Shows my ignorance I guess..:-/ I did try the CF that you suggested and the cell doesn't turn red immediately when you input the first number in the first cell which was what I wanted. However it stays yellow all the time now! It doesn't turn green when above 80 nor red below 80 even after those 5 cells have data in - it just stays yellow... No worries...probably I missed something else fundamental... Thanks for trying. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I didn't mean for you to change the formula in the cell. I meant for you to change the conditional formatting formula/rules. Since you're using "Cell value is", you'll have to change it to "Formula is" First format: =and(counta(c12:g12)=5,and(1<=h20,h20<80)) And second format: =and(counta(c12:g12)=5,h20=80) "Pheasant Plucker®" wrote: Hi Dave, Thanks for your quick reply. The existing formula in H20 is [=H17-H19] Conditional Formatting for H20 is two-fold; Cell Value between 1 - 79.99 Format pattern Red Cell Value greater than or equal to Format 80 pattern Green I did try what you suggested by prefixing the existing formula to give; [=AND(COUNTA(C12:G12)=5,H17-H19)) but then the word FALSE or TRUE appears in cell H20 which I do not want. Nice try - thanks anyway :-) Kind regards, -=pp=- "Dave Peterson" wrote in message ... Depending on what you used for your conditional formatting rules, maybe you could just add another requirement: =and(counta(c12:g12)=5,(yourexistingrules)) "Pheasant Plucker®" wrote: Does the subject even make sense? I have a cell H20 that when the spreadsheet is blank is coloured yellow to highlight the fact that it has a special purpose. When the spreadsheet is blank the number 0 is displayed in this cell. It has Conditional Formatting applied to it to turn the cell red and display the word FAIL if the summed result is between 1 and 79.99 For 80 and above the cell is coloured green and the word PASS is displayed in it. All great so far... However the summed result in this cell H20 depends upon the result of another cell H17 that in turn depends upon the input of 5 other cells C12:G12 I would rather H20 does not change colour until all of those 5 cells C12:G12 have data in them as it would be wrong to display FAIL in a red cell until the result of all 5 cells are input. Does that make sense and is this even possible? -- Regards, -pp- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |