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

Error:

The pivot table 'PivotTableX' could not be refreshed.
The following error was reported: 'Reference is not valid.'.

Solution:

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.

1. Open the workbook and 'Refresh' it to produce the error.
2. Right click on the sheet tabs in the workbook and choose 'Unhide' if it is available.
   
3. Unhide all of the hidden sheets (if not hidden sheets exist then move to #4).
4. Look through each sheet for a #VALUE.
   
5. Click on the #VALUE and press 'Debug' 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.