Forum Discussion

Copper Contributor

May 23, 2022

Pivot table missing sheet errors

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 dashboa...

Excel

flexyourdata
May 24, 2022
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.

flexyourdata

Iron Contributor

May 23, 2022

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.

athalekar
Copper Contributor
May 24, 2022
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.
- flexyourdata
  Iron Contributor
  May 24, 2022
  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.
  - athalekar
    Copper Contributor
    May 24, 2022
    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 !