#REF in Sheets that refer to Pivot Tables

Giganews Newsgroups
Subject: #REF in Sheets that refer to Pivot Tables
Posted by:  Will C (Will.C..28shvy_1149276601.49@excelforum-nospam.com)
Date: Fri, 2 Jun 2006

Hi All:

I have really enjoyed learing about pivot tables.  What a great and
easy tool!

Anyway, I have run into a vexing roadblock.  I am using an extensive
data base and various pivot tables to formulate a cash flow projection.
Everything works well.  However, on the summary sheet, I am getting the
dreaded #REF for any cell that refers to a sum on a pivot table that is

Let me explain.  Say I have a pivot table summing sales forecasts.  If
there is no forecasted sales for 2009 of a certain item, the sales
pivot table is blank for that year.  That is fine. (Note - I have
checked the 'show items with no data' checkbox on the field properties
of that field in the pivot table).

Now for the problem: If I directly refer to that spot on the pivot
table (i.e., the total of that year/item) on my summary worksheet, then
I get #REF! instead of zero.

I really want a zero instead of what looks like an error.

Is there a workaround?  Thanks in advance.


Will C.
Will C.'s Profile:http://www.excelforum.com/member.php?action=getinfo&userid=35051
View this thread:http://www.excelforum.com/showthread.php?threadid=547961