#REF! in Pivot Table when Input Data is Deleted and Refreshed PT

[johndrew]

I have a macro delete the data information and refresh the Pivot Table, I get #Ref!

I am using a macro to delete the data and refresh and using Excel10

It happens when I don't use all the fields in the Design input Box. There are times when I don't use all the fields.
For example sometimes I may not have coins. The pivot table is looking for an entry in the coin field. I have my report getting the total from the Pivot Table and in my report is where the #ref! appears. I can't post an attachment to show you to help understand my problem.

This is for a church. I have checks, currency and coins, name, amount and the fund to apply in the Design Box.
It will work if I make a dummy entry for all fields, but then they appear in the report. I can make an entry for a name "no body" make 5 entries to include all fields and filter "nobody" out but was hoping there is a better way.

 

[Andrew Poulsom]

What formula are you using that returns #REF!?

 

[johndrew]

Name Type Fund Amount
Tom Check Tithe $11.00
Bill Currency Missions $22.00
Henry Coins Missions $33.00
Sam Check Building $44.00

I have Combo Boxes to enter Type and Fund
and a Pivot Table on the data, with a breakdown of Type
The original Pivot Tables gives me what I need
The Type totals are pulled in from the PT into Cells, D10 (Checks),D11(Currency) and D12(Coins)

The next Sunday, I delete the data and leave the PT the same
In cells D10,D11 and D12 I will have #Ref! until data is entered into the field categories.
If no entry is made into the original Type field, it will give the error.
If that category is used it works.

Example: if Checks is used again, D10 will give the total, if Coins is not used, a #Ref! will stay in D12 and my totals will not work.
I need something to remove the #Ref! in my report if there isn't any value entered for that category.

 

[Andrew Poulsom]

What formula do you have in D10:D12?

 

[johndrew]

=+GETPIVOTDATA("Amt",$F$10,"TYPE"," Check")
=+GETPIVOTDATA("Amt",$F$10,"TYPE"," Currency")
=+GETPIVOTDATA("Amt",$F$10,"TYPE","Coins")

 

[Andrew Poulsom]

Does this work for you?

=IFERROR(GETPIVOTDATA("Amt",$F$10,"TYPE"," Check"),"")