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!

Hi!

Assume the data is in the range A1:B10

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

Biff

"CinqueTerra" wrote in message
...
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!

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.


"CinqueTerra" wrote:

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!

On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
wrote:

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!

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

Awesome! Thanks!

"Ron Rosenfeld" wrote:

On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
wrote:

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!

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

You're welcome.


On Wed, 26 Oct 2005 14:30:03 -0700, "CinqueTerra"
wrote:

Awesome! Thanks!

"Ron Rosenfeld" wrote:

On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
wrote:

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!

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


--ron

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:)


--
pat59
------------------------------------------------------------------------
pat59's Profile: http://www.excelforum.com/member.php...o&userid=31861
View this thread: http://www.excelforum.com/showthread...hreadid=479560