|
Conditional Formatting Question
I'm using 3 conditions for cell J7 which are as follows:
=AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
Try changing your first criteria to the following:
=OR(R23<=14,ERROR.TYPE(4)) I think that will do it for you. -- Kevin Backmann "Ken" wrote: I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
I inserted =OR(R23<=14,ERROR.TYPE(4)) but it didn't work.
.TYPE(4)) What is this part of the formula for? Ken "Kevin B" wrote: Try changing your first criteria to the following: =OR(R23<=14,ERROR.TYPE(4)) I think that will do it for you. -- Kevin Backmann "Ken" wrote: I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
=AND(R23<=14).....Green
=AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
R23=14
R2330 Will do. Well, not exactly. =R23=14 =R2330 *Those* will do! <g -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
For some reason it is not working. Maybe the Conditional Formatting will only
work with number values. I don't understand ... this should work. "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
This works
=ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
Try using =OR(R23<=14,ISERROR(R23))
-- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
When I try =OR(R23<=14,ISERROR(R23)) it just turns the cell white but if I
insert =ISERROR(R23) it turns the cell green. Very strange Regards, "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
Also,
In order to get =ISERROR(R23) to work in cell J7 I had to change the formula in cell R23 from =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(TotalTicket)'!$A$3,"REGION_ROLLUP","C ALA")),"0.0",GETPIVOTDATA("Max OpenAge",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) to GETPIVOTDATA("Max OpenAge",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA") I also deleted condition 2 and 3 to rule them out as being part of the problem. Regards, "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
Ok this is what I have so far...
Condition 1 =ISERROR(R23) Turns the cell green Condition 2 =AND(R2314,R23<=30) Turns the cell yellow Condition 3 =R2330 Turns the cell red The only problem is that I have a gap (which is the =R23<=14). I'm not sure why =OR(R23<=14,ISERROR(R23)) won't work. Is there another formula that would allow me to meet both criteria? What about SUMPRODUCT? "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
My fault!
I wasn't paying attention. Try this as condition 1 : =IF(ISERROR(R23),1,IF(AND(ISNUMBER(R23),R23<=14),1 )) Make sure you use it as shown with the ISERROR test first. The reason this didn't work: =OR(R23<=14,ISERROR(R23)) When R23 = #REF! ISERROR returns TRUE. However, R23<=14 still returns #REF! so that the OR evaluates like this: =OR(#REF!,TRUE) Which returns #REF! thereby not applying the format. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok this is what I have so far... Condition 1 =ISERROR(R23) Turns the cell green Condition 2 =AND(R2314,R23<=30) Turns the cell yellow Condition 3 =R2330 Turns the cell red The only problem is that I have a gap (which is the =R23<=14). I'm not sure why =OR(R23<=14,ISERROR(R23)) won't work. Is there another formula that would allow me to meet both criteria? What about SUMPRODUCT? "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
You are the man!! That worked like a charm. Thanks for sticking with me on
this one... I greatly appreciate it. Best Regards, "T. Valko" wrote: My fault! I wasn't paying attention. Try this as condition 1 : =IF(ISERROR(R23),1,IF(AND(ISNUMBER(R23),R23<=14),1 )) Make sure you use it as shown with the ISERROR test first. The reason this didn't work: =OR(R23<=14,ISERROR(R23)) When R23 = #REF! ISERROR returns TRUE. However, R23<=14 still returns #REF! so that the OR evaluates like this: =OR(#REF!,TRUE) Which returns #REF! thereby not applying the format. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok this is what I have so far... Condition 1 =ISERROR(R23) Turns the cell green Condition 2 =AND(R2314,R23<=30) Turns the cell yellow Condition 3 =R2330 Turns the cell red The only problem is that I have a gap (which is the =R23<=14). I'm not sure why =OR(R23<=14,ISERROR(R23)) won't work. Is there another formula that would allow me to meet both criteria? What about SUMPRODUCT? "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
Conditional Formatting Question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ken" wrote in message ... You are the man!! That worked like a charm. Thanks for sticking with me on this one... I greatly appreciate it. Best Regards, "T. Valko" wrote: My fault! I wasn't paying attention. Try this as condition 1 : =IF(ISERROR(R23),1,IF(AND(ISNUMBER(R23),R23<=14),1 )) Make sure you use it as shown with the ISERROR test first. The reason this didn't work: =OR(R23<=14,ISERROR(R23)) When R23 = #REF! ISERROR returns TRUE. However, R23<=14 still returns #REF! so that the OR evaluates like this: =OR(#REF!,TRUE) Which returns #REF! thereby not applying the format. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok this is what I have so far... Condition 1 =ISERROR(R23) Turns the cell green Condition 2 =AND(R2314,R23<=30) Turns the cell yellow Condition 3 =R2330 Turns the cell red The only problem is that I have a gap (which is the =R23<=14). I'm not sure why =OR(R23<=14,ISERROR(R23)) won't work. Is there another formula that would allow me to meet both criteria? What about SUMPRODUCT? "Kevin B" wrote: Try using =OR(R23<=14,ISERROR(R23)) -- Kevin Backmann "Ken" wrote: This works =ISERROR(R23) Now I just need to add in R23<=14 somehow. Regards, "T. Valko" wrote: =AND(R23<=14).....Green =AND(R2330).....Red You don't need the AND function in those. R23=14 R2330 Will do. For your problem with #REF! change: =AND(R23<=14) To: =OR(ISERROR(R23),R23<=14) That will apply to *all* errors, not just #REF! -- Biff Microsoft Excel MVP "Ken" wrote in message ... I'm using 3 conditions for cell J7 which are as follows: =AND(R23<=14) Green =AND(R2314,R23<=30) Yellow =AND(R2330) Red This works fine if there are no errors in cell R23. If there is an error in cell R23 the cell turns red. (Error being #REF!) My question is this ... Is there a way to turn cell J7 green if cell R23 displays #REF! ? There are times when cell R23 displays #REF! due to the fact that the pivot didn't have data to return. The formula I have in cell R23 is as follows: =IF(ISERROR(GETPIVOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")),"0.0",GETPI VOTDATA("Max Open Age",'Pivot1(Total Ticket)'!$A$3,"REGION_ROLLUP","CALA")) Using the above error formula does populate cell R23 with 0.0 but I can't get the conditional formatting in J7 to recognize it. Can anyone help? |
| All times are GMT +1. The time now is 06:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com