return the message #reference!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

E Petersen - 22 Mar 2006 11:37 GMT

I am using the function getpivotdata in excel, and I ask for the total amount
on diffrent acounts I have in my pivottabel. If there are no data on the
acount I get the answer #reference! because the acount not exist in my
pivottabel. Insted I want to get 0.
How can I get this result?

Reply to this Message

Ken Wright - 24 Mar 2006 10:03 GMT

Try

=IF(ISERROR(Your_formula),0,your_formula)

Note though that this will trap all errors so may hide some other error as
well.

Signature

Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

>I am using the function getpivotdata in excel, and I ask for the total
>amount
> on diffrent acounts I have in my pivottabel. If there are no data on the
> acount I get the answer #reference! because the acount not exist in my
> pivottabel. Insted I want to get 0.
> How can I get this result?

Reply to this Message

E Petersen - 27 Mar 2006 14:40 GMT

Tanks for your answering. The formel woun't work in my excel. Instet I have
done this: =HVIS(ER.FEJL(-GETPIVOTDATA(Pivottabeller!$A$1:$O$20;$A8&"
"&C$6));0;-GETPIVOTDATA(Pivottabeller!$A$1:$O$20;$A8&" "&C$6)). In english
the formel will start with: =If(is.fail(...

> Try
>
[quoted text clipped - 9 lines]
> > pivottabel. Insted I want to get 0.
> > How can I get this result?

Reply to this Message

Ken Wright - 27 Mar 2006 21:02 GMT

Thats OK, it's a similar solution.

Regards
Ken................

> Tanks for your answering. The formel woun't work in my excel. Instet I
> have
[quoted text clipped - 17 lines]
>> > pivottabel. Insted I want to get 0.
>> > How can I get this result?

Reply to this Message

Barb Reinhardt - 27 Mar 2006 14:22 GMT

Try

=if(iserror(getpivottable ...)=TRUE,0,getpivottable...)

> I am using the function getpivotdata in excel, and I ask for the total amount
> on diffrent acounts I have in my pivottabel. If there are no data on the
> acount I get the answer #reference! because the acount not exist in my
> pivottabel. Insted I want to get 0.
> How can I get this result?

Reply to this Message