How to fix the error: “#REF”
This issue occurs because the GETPIVOTDATA formula that is automatically generated by Excel is incorrect. When there is a problem with custom subtotals in PivotTables in Microsoft Excel 2002, this error arises. You must edit the incorrect formula to get rid of this problem.
The solution to the problem
Follow the instructions to fix the error: “#REF”:
Double-click the cell where #REF is displayed.
Delete 'Data' from the following formula that Excel generates to create the correct formula:
Incorrect formula: GETPIVOTDATA(, ''[Data,]'')
Correct formula: GETPIVOTDATA(, '''')
In the formula is the absolute reference to the top left cell of the PivotTable, is the name of the data group for which the subtotal is being calculated, is the name of the particular item being subtotaled, is the name of the function used in the subtotal, for example, sum or average.