Overview
When opening a workbook containing a PivotTable which references either an NL(Table) or NL(Rows) report for its data source the following error can be displayed:
Complete text of error message is:
The pivot table 'PivotTableX' could not be refreshed.
The following error was reported: 'Reference is not valid.'.
Cause:
When a workbook contains a PivotTable that references either an NL(Table) or NL(Rows) report for its data source and the NL(Table) or NL(Rows) report produces a #VALUE then this error will be displayed.
Resolution Process:
-
Open the workbook and Run it to produce the error.
-
Right click on the sheet tabs in the workbook and choose Unhide if it is available.
-
Unhide all of the hidden sheets (if no hidden sheets exist then move to #4).
-
Look through each sheet for a #VALUE.
-
Click on the #VALUE and press Check Error which will reveal and error as to why the report did not refresh properly.
-
Switch the report into Design Mode and troubleshoot the source of that error.
Comments