Error: PivotTable report cannot overlap another PivotTable Report

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
69
Hey all. I've found where other people have had this error but those solutions haven't helped me solve this one.

Long story short I built this for another employee about a year ago and my memory is fuzzy on some specifics.

There's only one PivotTable on the sheet.

I get the typical error message when I hit refresh.

The weird part is that I can move around my fields, remove, add, etc except for one field. It looks like I created some sort of calculated field of a date field. It's named "Years" and it has a little filter icon beside it in the PivotTable Fields area. When I try to remove that field from the Rows section, I get the overlap error message again. Does the same thing when I try to move that field to a filter, value, or column area.

Any thoughts or ways I could troubleshoot this thing?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,828
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Add this code to a new module in your workbook and tell us what the message says:

Code:
Sub HowManyPivots()
msgbox activesheet.pivottables.count
end sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,828
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Are there other pivot tables in the same workbook that use the same source data?
 

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
69
Ok now we're on to something. When I delete all other pivot tables in the workbook (they're on separate sheets), I don't get the error anymore but the "Years" field goes away as well.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,828
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Right click your date field in the pivot table and choose Group, then include Years in the grouping. That should bring back the Years field. Now you just need to figure out which of the other pivot tables was behind the error message. Did you have more than one pivot on any of your sheets?
 

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
69
Figured out which sheet was causing it. For some reason though when I go back and try to Group by Year, it tells me "Cannot group that selection". Edit - It can group but it basically just puts all the data up under "Group1". Edit2: Got it to work finally. Had to do some data integrity checks.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,096,444
Messages
5,450,482
Members
405,613
Latest member
Arpit

This Week's Hot Topics

Top