Summarising Pivot Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1270559%22%20slang%3D%22en-US%22%3ESummarising%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1270559%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20some%20help%20with%20a%20spreadsheet%20that%20I%20am%20currently%20working%20on.%20I%20have%20several%20tabs%20with%20data%20about%20staff%20absences%20at%20different%20levels%20across%20multiple%20schools%20re%20coronavirus.%20For%20each%20level%20of%20employee%2C%20or%20per%20tab%2C%20I%20have%20created%20pivot%20tables%20to%20show%20what%20the%20FTE%20headcount%20is%20normally%20and%20what%20it%20is%20while%20employees%20are%20absent%20due%20to%20CV.%20I%20then%20created%20a%20summary%20tab%20to%20bring%20all%20the%20schools%20back%20to%20together%20with%20totals%20for%20each%20employee%20level%20based%20on%20each%20of%20the%20pivot%20tables.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20summary%20sheet%20worked%20fine%20to%20start%20with%20and%20updated%20as%20soon%20as%20I%20refreshed%20the%20pivot%20tables.%20But%20that%20has%20now%20stopped%20working%20and%20I%20can't%20understand%20why.%20Can%20anyone%20offer%20any%20suggestions%20to%20me%20on%20how%20I%20fix%20this%20problem%3F%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1270559%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271392%22%20slang%3D%22en-US%22%3ERe%3A%20Summarising%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271392%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F293583%22%20target%3D%22_blank%22%3E%40jasonm69%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20a%20direct%20answer%20to%20the%20question%20you%20posed.%20My%20own%20approach%20would%20have%20been%20different%20from%20the%20start%2C%20and%20I'm%20wondering%20if%20it%20might%20also%20have%20avoided%20the%20problem.%3C%2FP%3E%3CP%3EThat%20would%20have%20been%20to%20%3CU%3E%3CEM%3Ecreate%20your%20database%20as%20a%20single%20table%3C%2FEM%3E%3C%2FU%3E%2C%20a%26nbsp%3B%20table%20in%20which%2C%20as%20needed%2C%20you%20use%20a%20column%20to%20identify%20level%2C%20a%20column%20to%20identify%20school%2C%20a%20column%20for....%20whatever%20it%20is%20that%20is%20leading%20you%20to%20create%20separate%20tabs%20that%20ultimately%20you%20want%20to%20bring%20back%20together%20in%20a%20summary.%3C%2FP%3E%3CP%3EKeep%20them%20together%20to%20begin%20with%2C%20in%20a%20single%20database%20(Table).%20You%20can%20then%20use%20%3CU%3EPivot%20Table's%20%3CSTRONG%3EFilter%3C%2FSTRONG%3E%20capability%3C%2FU%3E%20to%20alter%20the%20display%20for%20any%20specific%20combination%20of%20school%2Flevel%20that%20you%20wish%2C%20including%20ALL....%3C%2FP%3E%3CP%3EThen%20a%20refresh%20of%20the%20data%20would%20be%20readily%20handled.%20If%20you%20create%20the%20Table%20as%20a%20true%20Excel%20Table%2C%20it%20will%20automatically%20add%20new%20rows%20into%20the%20range%20included%20in%20the%20Pivot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20such%20an%20approach%20still%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Good morning 

I need some help with a spreadsheet that I am currently working on. I have several tabs with data about staff absences at different levels across multiple schools re coronavirus. For each level of employee, or per tab, I have created pivot tables to show what the FTE headcount is normally and what it is while employees are absent due to CV. I then created a summary tab to bring all the schools back to together with totals for each employee level based on each of the pivot tables. 

The summary sheet worked fine to start with and updated as soon as I refreshed the pivot tables. But that has now stopped working and I can't understand why. Can anyone offer any suggestions to me on how I fix this problem?

Many thanks in advance.

1 Reply
Highlighted

@jasonm69 

 

I don't have a direct answer to the question you posed. My own approach would have been different from the start, and I'm wondering if it might also have avoided the problem.

That would have been to create your database as a single table, a  table in which, as needed, you use a column to identify level, a column to identify school, a column for.... whatever it is that is leading you to create separate tabs that ultimately you want to bring back together in a summary.

Keep them together to begin with, in a single database (Table). You can then use Pivot Table's Filter capability to alter the display for any specific combination of school/level that you wish, including ALL....

Then a refresh of the data would be readily handled. If you create the Table as a true Excel Table, it will automatically add new rows into the range included in the Pivot.

 

Is such an approach still possible?