Home

Refreshing pivot table problems

%3CLINGO-SUB%20id%3D%22lingo-sub-289364%22%20slang%3D%22en-US%22%3ERefreshing%20pivot%20table%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289364%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20work%20in%20a%20school%20and%20have%20a%20spreadsheet%20which%20has%20a%20year%20group%20main%20data%20sheet%2C%20columns%20A-GR%20and%20rows%201-285.%3C%2FP%3E%3CP%3EI%20then%20have%20a%20data%20sheet%20per%20subject%20which%20populates%20from%20the%20main%20data%20sheet%20mentioned%20above.%3C%2FP%3E%3CP%3EEach%20subject%20then%20has%20a%20subject%20summary%20sheet%20which%20has%20multiple%20pivot%20tables.%20The%20data%20source%20for%20the%20pivot%20tables%20on%20the%20subject%20summary%20sheet%20is%20the%20subject%20data%20sheet.%3C%2FP%3E%3CP%3ESome%20of%20the%20pivot%20tables%20use%202%20elements%20to%20calculate%20averages.%20Other%20pivot%20tables%20have%203%20elements.%3C%2FP%3E%3CP%3EWe%20have%20a%20data%20drop%203%20times%20per%20year%20and%20the%20intention%20is%20that%20I%20simply%20paste%20the%20new%20data%20onto%20the%20main%20year%20group%20data%20sheet%20which%20then%20updates%20on%20the%20subject%20data%20sheets.%20This%20works%20fine.%3C%2FP%3E%3CP%3EWhen%20I%20refresh%20the%20pivot%20tables%20however%2C%20those%20with%20only%202%20elements%20refresh%20fine%20but%20those%20with%203%20drop%20one%20of%20the%20elements%20%26amp%3B%20I%20have%20to%20insert%20all%20of%20the%20pivot%20tables%20afresh%20all%20over%20again%2C%20every%20time%20which%20takes%20days!%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20suggestions%20please%3F%3C%2FP%3E%3CP%3EKind%20regards%20and%20much%20appreciated.%3C%2FP%3E%3CP%3ExVx%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-289364%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289401%22%20slang%3D%22en-US%22%3ERe%3A%20Refreshing%20pivot%20table%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289401%22%20slang%3D%22en-US%22%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F141503%22%20target%3D%22_blank%22%3E%40ahmad%20ali%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20green%20marks%20in%20(the%20sheets%20that%20their%20pivot%20tables%20do%20not%20get%20updated)%20that%20says%3A%20inconsistent%20formula%3F%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3ENo.%20All%20source%20data%20is%20working%20perfectly.%3C%2FP%3E%3CP%3EI%20have%20discovered%20a%20quicker%20way%20to%20correct%20by%20re-selecting%20the%20'lost'%20field%20in%20the%20pivot%20table%20fields%20then%20I%20have%20to%20select%20Field%20Settings%20%26amp%3B%20re-select%20what%20I%20want%20it%20to%20show.%3C%2FP%3E%3CP%3EThis%20is%20much%20quicker%20than%20having%20to%20re-do%20thew%20whole%20pivot%20table%20again%20but%20I%20cannot%20fathom%20why%20it%20drops%20it%20in%20the%20first%20place%20when%20the%20data%20in%20the%20data%20source%20is%20updated%20and%20the%20pivot%20tables%20refreshed.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-289393%22%20slang%3D%22en-US%22%3ERe%3A%20Refreshing%20pivot%20table%20problems%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-289393%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20green%20marks%20in%20(the%20sheets%20that%20their%20pivot%20tables%20do%20not%20get%20updated)%20that%20says%3A%20inconsistent%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
V0nN1
New Contributor

Hi,

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!

Does anyone have any suggestions please?

Kind regards and much appreciated.

xVx

2 Replies

Hello,

 

Do you have any green marks in (the sheets that their pivot tables do not get updated) that says: inconsistent formula?


@ahmad ali wrote:

Hello,

 

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.


Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies