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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Hello again Dave,
Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Any cell in C12:G12 or all the cells in c12:g12.
Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Hey Dave,
Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
I don't quite understand.
If your formula (in the cell, not format|conditional formatting) looks like this: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Then it should ok. You may want to post your formulas and your criteria once more. "Pheasant Plucker®" wrote: Hey Dave, Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Thanks for your patience Dave,
The result in H22 depends upon the result displayed in H20 which in turn depends upon data entered in C12:G12 With me so far? ;^) The formula in H22 is exactly as you stated below; =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) The Conditional Formatting in H22 is set as follows; Condition 1 Formula=AND(COUNTA(C12:G12)=5,H22="FAIL") is set to pattern RED Condition 2 Formula=AND(COUNTA(C12:G12)=5,H22="PASS") is set to pattern GREEN The basic H22 Format Cells is set as follows; Number - General Font - Bold Pattern - Yellow Locked At 'rest' with no data input the cell is coloured YELLOW with no text (good) When I enter the first number in C12 which is a maximum of 25 then H22 stays YELLOW (good) but the word FAIL is displayed (bad) This I believe is being displayed because of the formula in H22 as at this point H20 will always be below 75 The difficult thing is that until all 5 cells (C12:G12) have data in them I want H22 not to display FAIL (or PASS for that matter) Obviously when data is entered in all cells C12:G12 then it works as expected; H20 is below 75 then H22 turns RED & displays FAIL (good) H20 is 75 or above then H22 turns GREEN & displays PASS (good) Does that make sense Dave? Thanks again. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I don't quite understand. If your formula (in the cell, not format|conditional formatting) looks like this: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Then it should ok. You may want to post your formulas and your criteria once more. "Pheasant Plucker®" wrote: Hey Dave, Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Maybe adding another check to the formula in the cell:
=IF(H20=0,"",IF(H20<75,"FAIL","PASS")) becomes =IF(or(counta(c12:g12)<5,H20=0),"",IF(H20<75,"FAIL ","PASS")) "Pheasant Plucker®" wrote: Thanks for your patience Dave, The result in H22 depends upon the result displayed in H20 which in turn depends upon data entered in C12:G12 With me so far? ;^) The formula in H22 is exactly as you stated below; =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) The Conditional Formatting in H22 is set as follows; Condition 1 Formula=AND(COUNTA(C12:G12)=5,H22="FAIL") is set to pattern RED Condition 2 Formula=AND(COUNTA(C12:G12)=5,H22="PASS") is set to pattern GREEN The basic H22 Format Cells is set as follows; Number - General Font - Bold Pattern - Yellow Locked At 'rest' with no data input the cell is coloured YELLOW with no text (good) When I enter the first number in C12 which is a maximum of 25 then H22 stays YELLOW (good) but the word FAIL is displayed (bad) This I believe is being displayed because of the formula in H22 as at this point H20 will always be below 75 The difficult thing is that until all 5 cells (C12:G12) have data in them I want H22 not to display FAIL (or PASS for that matter) Obviously when data is entered in all cells C12:G12 then it works as expected; H20 is below 75 then H22 turns RED & displays FAIL (good) H20 is 75 or above then H22 turns GREEN & displays PASS (good) Does that make sense Dave? Thanks again. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I don't quite understand. If your formula (in the cell, not format|conditional formatting) looks like this: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Then it should ok. You may want to post your formulas and your criteria once more. "Pheasant Plucker®" wrote: Hey Dave, Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Dave,
You're a genius! You wouldn't believe how many times I tried to enter a 'similar' formula into H22 only to have Excel throw up its hands in disgust... I tried everything I could think of drawing on what knowledge I have gleaned from this ng in general, you in particular but to no avail. Many thanks...it is much appreciated. Kind regards, -=pp=- "Dave Peterson" wrote in message ... Maybe adding another check to the formula in the cell: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) becomes =IF(or(counta(c12:g12)<5,H20=0),"",IF(H20<75,"FAIL ","PASS")) "Pheasant Plucker®" wrote: Thanks for your patience Dave, The result in H22 depends upon the result displayed in H20 which in turn depends upon data entered in C12:G12 With me so far? ;^) The formula in H22 is exactly as you stated below; =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) The Conditional Formatting in H22 is set as follows; Condition 1 Formula=AND(COUNTA(C12:G12)=5,H22="FAIL") is set to pattern RED Condition 2 Formula=AND(COUNTA(C12:G12)=5,H22="PASS") is set to pattern GREEN The basic H22 Format Cells is set as follows; Number - General Font - Bold Pattern - Yellow Locked At 'rest' with no data input the cell is coloured YELLOW with no text (good) When I enter the first number in C12 which is a maximum of 25 then H22 stays YELLOW (good) but the word FAIL is displayed (bad) This I believe is being displayed because of the formula in H22 as at this point H20 will always be below 75 The difficult thing is that until all 5 cells (C12:G12) have data in them I want H22 not to display FAIL (or PASS for that matter) Obviously when data is entered in all cells C12:G12 then it works as expected; H20 is below 75 then H22 turns RED & displays FAIL (good) H20 is 75 or above then H22 turns GREEN & displays PASS (good) Does that make sense Dave? Thanks again. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I don't quite understand. If your formula (in the cell, not format|conditional formatting) looks like this: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Then it should ok. You may want to post your formulas and your criteria once more. "Pheasant Plucker®" wrote: Hey Dave, Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deferring conditional formatting?
Glad you got it working.
"Pheasant Plucker®" wrote: Dave, You're a genius! You wouldn't believe how many times I tried to enter a 'similar' formula into H22 only to have Excel throw up its hands in disgust... I tried everything I could think of drawing on what knowledge I have gleaned from this ng in general, you in particular but to no avail. Many thanks...it is much appreciated. Kind regards, -=pp=- "Dave Peterson" wrote in message ... Maybe adding another check to the formula in the cell: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) becomes =IF(or(counta(c12:g12)<5,H20=0),"",IF(H20<75,"FAIL ","PASS")) "Pheasant Plucker®" wrote: Thanks for your patience Dave, The result in H22 depends upon the result displayed in H20 which in turn depends upon data entered in C12:G12 With me so far? ;^) The formula in H22 is exactly as you stated below; =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) The Conditional Formatting in H22 is set as follows; Condition 1 Formula=AND(COUNTA(C12:G12)=5,H22="FAIL") is set to pattern RED Condition 2 Formula=AND(COUNTA(C12:G12)=5,H22="PASS") is set to pattern GREEN The basic H22 Format Cells is set as follows; Number - General Font - Bold Pattern - Yellow Locked At 'rest' with no data input the cell is coloured YELLOW with no text (good) When I enter the first number in C12 which is a maximum of 25 then H22 stays YELLOW (good) but the word FAIL is displayed (bad) This I believe is being displayed because of the formula in H22 as at this point H20 will always be below 75 The difficult thing is that until all 5 cells (C12:G12) have data in them I want H22 not to display FAIL (or PASS for that matter) Obviously when data is entered in all cells C12:G12 then it works as expected; H20 is below 75 then H22 turns RED & displays FAIL (good) H20 is 75 or above then H22 turns GREEN & displays PASS (good) Does that make sense Dave? Thanks again. Kind regards, -=pp=- "Dave Peterson" wrote in message ... I don't quite understand. If your formula (in the cell, not format|conditional formatting) looks like this: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Then it should ok. You may want to post your formulas and your criteria once more. "Pheasant Plucker®" wrote: Hey Dave, Many thanks for the quick reply. Don't forget you are dealing with a complete muppet when it comes to Excel ;^) If I understand you correctly I need to replace my existing Conditional Formatting where I am using Cell Value to your Conditional Formatting Formula below? When I do that it works to a degree - H22 now stays Yellow until C12:G12 are all filled in (Terrific!) when it then turns Green or Red displaying Pass or Fail depending upon whether the pass mark of 75 has been achieved. The only fly in the ointment is that when I start to fill in data in C12:G12 then H22 (although it now stays Yellow :-) still displays the word FAIL I was hoping to delay the word Pass or Fail until all the cells C12:G12 were filled in. In my ignorance the problem now seems to lie in the formula for H22 as when it sees anything below 75 in H20 it is going to display a FAIL even before all the cells C12:G12 have been completed. Is there any way to defer the H22 formula until all the Cells C12:G12 have been filled in please? Thanks again & kind regards, -=pp=- "Dave Peterson" wrote in message ... Any cell in C12:G12 or all the cells in c12:g12. Since you weren't using a formula--just a comparison: Cell Value Is equal to ="FAIL" format pattern RED In either case, maybe you can add one more condition to your format|condional formatting formulas: =and(counta(c12:g12)=5,h22="fail") or =and(counta(c12:g12)0,h22="fail") Same kind of thing with Pass. "Pheasant Plucker®" wrote: Hello again Dave, Don't know if you are still reading this thread...sorry for the delay in replying - I have only got around to working with this spreadsheet again as I have a teleconference booked for Monday where I need to present some stuff including this spreadsheet. :-/ Shall I put your name in the credits? ;^) OK...I did input your suggestions into the Conditional Formatting in Cell H20 and it does work exactly as you stated - many thanks. Incidentally I also lowered the pass mark from 80 to 75 The one problem I have now is Cell H22 that is setup as follows; At 'rest' this will display nothing but a YELLOW pattern - no text. When below the H20 pass mark of 75 it displays FAIL on a RED pattern When equal to or above the H20 pass mark of 75 it displays PASS on a GREEN pattern. What I really need is for it not to change its default behaviour of displaying no text on a yellow background until those cells C12:G12 have been completed - much as we deferred H20 to do nothing until C12:G12 have all been completed and then display as above (either FAIL on a RED pattern if H20 is under 75 or PASS on a GREEN pattern if H20 is 75 or over). Does that make sense? If it helps H22 currently has the following attributes; Formula: =IF(H20=0,"",IF(H20<75,"FAIL","PASS")) Conditional Formatting: Cell Value Is equal to ="'FAIL" format pattern RED Cell Value Is equal to ="'PASS" format pattern GREEN Your (or any other:-) help would be much appreciated! Thanks & kind regards, -=pp=- "Dave Peterson" wrote in message ... 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 -- Dave Peterson -- 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) |