Conditional Format #REF!

[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!

[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

"CinqueTerra" <[email protected]> wrote in message
news:[email protected]...
>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!

[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.


"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!

[Ron Rosenfeld]

On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
<[email protected]> 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

[CinqueTerra]

Awesome! Thanks!

"Ron Rosenfeld" wrote:

> On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
> <[email protected]> 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 Rosenfeld]

You're welcome.


On Wed, 26 Oct 2005 14:30:03 -0700, "CinqueTerra"
<[email protected]> wrote:

>Awesome! Thanks!
>
>"Ron Rosenfeld" wrote:
>
>> On Wed, 26 Oct 2005 11:05:08 -0700, "CinqueTerra"
>> <[email protected]> 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

[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