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.
- Open the workbook and 'Refresh' 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 'Debug' 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