Conditional Format #REF!

C

CinqueTerra

I am populating a report with GetPivotData. If the data does not exist in
the Pivot, #REF! is returned. Is there a way to use Conditional Format to
change the font to white when #REF! is returned by the formula?

Thanks in Advance!
 
B

Biff

Hi!

Assume the data is in the range A1:B10

Select that range
Goto Format>Conditional Formatting
Formula is: =ISERROR(A1)
Click the Format button
Set the font color to be the same as the background color
OK out

Biff
 
E

Elkar

In Conditional Formatting, change the type to "Formula" and enter:
=IsError(A1) Then change your font color.

Replace A1 with whatever cell contains the data you're checking.
 
R

Ron Rosenfeld

I am populating a report with GetPivotData. If the data does not exist in
the Pivot, #REF! is returned. Is there a way to use Conditional Format to
change the font to white when #REF! is returned by the formula?

Thanks in Advance!
Click to expand...

Select the range of cells that need to be formatted.

Format/Conditional Formatting:

Formula Is:
=ERROR.TYPE(cell_ref)=4

(Replace cell_ref with the selected active cell)

Format/Font and select Color: White

OK
OK


--ron
 
C

CinqueTerra

Awesome! Thanks!

Ron Rosenfeld said:
Select the range of cells that need to be formatted.

Format/Conditional Formatting:

Formula Is:
=ERROR.TYPE(cell_ref)=4

(Replace cell_ref with the selected active cell)

Format/Font and select Color: White

OK
OK


--ron
Click to expand...
 
P

pat59

Hi all,
This is the first time I use the forum. It seems to be very interesting
and useful, so I'm going to ask for a little help..
I tried to use the conditional formatting in order to eliminate #REF
error (due to lack of a certain item in pivot, that I'm going to
reference with GETPIVOTDATA) but I still have the #REF as the result of
sum several items.
Is there any solution to solve this issue?
Thanks in advance and best regards,

Pat:)
 
Top