Forum Discussion
Need help with changing data source of Pivot table
Hello All!
I have been trying to figure this out for a few months now. I am trying to update the data source of a pivot table but I keep getting the following message: "There's already data in <name of the sheet where the current Pivot table is>. Do you want to replace it?"
The weird thing is, it does not happen when using older data that is already in the workbook.
Below is what it looks like when it works. The groups stay in place and data changes. The data next to the pivot table also updates.
NOTE: the pivot table has the following characteristics:
1. The data are grouped and collapsed.
2. There are data right next to the pivot table.
3. There are also charts right below it.
These are the steps I'm doing:
1. My goal is, instead of creating a new pivot table every week, I'm trying to copy the existing sheet (will call it Pivot Sheet) that has the PV and rename it. And to also not disturb the other data around it.
2. I add another sheet to paste the new data set. (will call this Data Sheet)
3. On the Pivot Sheet, I try to change the "data source" of the Pivot Table to the new Data Sheet.
4. However, I get the error mentioned above. If I click on "Yes", some of the groups will ungroup and overlap the data to its right and completely wipes it out. I have to ungroup and group new data to get it back to normal.
If I click on "No" or hit the [x] to close the message, it will just have a few data outside of the groups but not all of it.
Steps taken to resolve it:
1. Confirmed that the new Data Sheet has the same exact headers as the working one.
2. Removed/broke data links that have "unknown status"
3. Moved the Pivot table to where it's on it's own and there are no other data around it, but issue persists.
3. Even copied over new data to the working Data Sheet, then just hit "refresh" on the pivot table, but the same exact message is reported and same issue happens.
4. I have researched for a long time now and followed recommendations, but no good.
Has anyone seen this before? Any suggestions are welcome!
Thank you all for your time!
- Hadji
2 Replies
gamilh Pivot tables may extend away from its origin (right and down). That's why you should never place anything below or to the right of a pivot table, unless you are 100% sure that it will never expand as much so that it will overlap existing data.
Redesign you report so that the added calculations and charts are placed above or towards the left of the pivot table.
Riny_van_Eekelen Thank you for your response and time! That is my plan B right now. I was hopeful that I can find something to make it work. At this point, your suggestion is what I would probably have to do.
Again, thank you for your time!
