Pivot table missing sheet errors

[athalekar]

I have prepared dashboard using a data source. While doing so i prepared few pivot tables from different data sheets where appropriate data was store. Now due to continuous improvement of the dashboard, i removed, merged sheets to form a consolidated single sheet which is my data source. 

Problem is, now when i make changes to the data, i use "Refresh all" from pivot table menu, i get errors about missing sheets which i used during the process.

Question : Is there way to clear these invalid errors and have seamless refresh all option ?

[#2]

You need to re-point each pivot table to the new merged table.

Follow this for each pivot:

1. Select any cell in the pivot table
2. Go to PivotTable Analyze>Data>Change Data Source
3. Select the new data source

After you've done this, it should refresh without problem.

[#3]

@flexyourdata This doesn't work. I have analyzed all my data sources from which I prepared either pivot charts or pivot tables. When I refresh all, it still shows error of a sheet which I deleted long back. 

[#4 · Accepted solution]

Please go to Data>Queries and Connections and check there are no connections to external files that are no longer needed. Additionally, ensure there are no hidden sheets containing pivot tables which are still pointing at the deleted data source.

It would help if you could upload the file or perhaps add some screenshots to show more context.

[#5]

@flexyourdata Nice. this pointer helped me to track the cached data sheet which i had deleted. I deleted the data connection and XLS is now showing no errors !

Thanks for the pointer !