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?