Conditional Formatting Question

[Ken]

I'm using 3 conditions for cell J7 which are as follows:

=AND(R23<=14) Green
=AND(R23>14,R23<=30) Yellow
=AND(R23>30) 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",GETPIVOTDATA("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?

 

[Kevin B]

Try changing your first criteria to the following:

=OR(R23<=14,ERROR.TYPE(4))

I think that will do it for you.

 

[Ken]

I inserted =OR(R23<=14,ERROR.TYPE(4)) but it didn't work.

.TYPE(4)) What is this part of the formula for?

Ken

 

[T. Valko]

=AND(R23<=14).....Green

=AND(R23>30).....Red

You don't need the AND function in those.

R23>=14
R23>30

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!

 

[T. Valko]

R23>=14

R23>30
Will do.

Well, not exactly.

=R23>=14
=R23>30

*Those* will do! <g>

 

[Ken]

For some reason it is not working. Maybe the Conditional Formatting will only
work with number values.

I don't understand ... this should work.

 

[Ken]

This works

=ISERROR(R23)

Now I just need to add in R23<=14 somehow.


Regards,

 

[Kevin B]

Try using =OR(R23<=14,ISERROR(R23))

 

[Ken]

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,

 

[Ken]

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","CALA")),"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,

 

[Ken]

Ok this is what I have so far...

Condition 1
=ISERROR(R23) Turns the cell green

Condition 2
=AND(R23>14,R23<=30) Turns the cell yellow

Condition 3
=R23>30 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?

 

[T. Valko]

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.

 

[Ken]

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]

You're welcome. Thanks for the feedback!