Error: The Pivot Table 'PivotTableX' Could Not Be Refreshed...'Reference is not valid

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:

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:

1. Open the workbook and Run it to produce the error.
2. Right click the sheet tabs in the workbook and choose Unhide if it is available.

   

3. Unhide all of the hidden sheets (if no hidden sheets exist then move to #4).
4. Look through each sheet for a #VALUE.

   

5. Click the #VALUE and press Check Error which will reveal and error as to why the report did not refresh properly.

   

6. Switch the report into Design Mode and troubleshoot the source of that error.