I work in a school and have a spreadsheet which has a year group main data sheet, columns A-GR and rows 1-285.
I then have a data sheet per subject which populates from the main data sheet mentioned above.
Each subject then has a subject summary sheet which has multiple pivot tables. The data source for the pivot tables on the subject summary sheet is the subject data sheet.
Some of the pivot tables use 2 elements to calculate averages. Other pivot tables have 3 elements.
We have a data drop 3 times per year and the intention is that I simply paste the new data onto the main year group data sheet which then updates on the subject data sheets. This works fine.
When I refresh the pivot tables however, those with only 2 elements refresh fine but those with 3 drop one of the elements & I have to insert all of the pivot tables afresh all over again, every time which takes days!
Do you have any green marks in (the sheets that their pivot tables do not get updated) that says: inconsistent formula?
No. All source data is working perfectly.
I have discovered a quicker way to correct by re-selecting the 'lost' field in the pivot table fields then I have to select Field Settings & re-select what I want it to show.
This is much quicker than having to re-do thew whole pivot table again but I cannot fathom why it drops it in the first place when the data in the data source is updated and the pivot tables refreshed.