SOLVED

Hide Pivot Table in a sheet affects other formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-2712261%22%20slang%3D%22en-US%22%3EHide%20Pivot%20Table%20in%20a%20sheet%20affects%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712261%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20few%20Pivot%20Tables%20in%20a%20sheet%2C%20then%20I%20have%20their%20totals%20grouped%20in%20another%20%22overall%20summary%22%20table.%3C%2FP%3E%3CP%3EThe%20idea%20is%20that%20you%20see%20this%20small%205-row%20table%20with%20totals%20and%20down%20in%20the%20sheet%20(on%20their%20own%20separate%20table)%20you%20have%20the%20breakdown%20of%20each%20total.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20hide%20all%20those%20tables%2C%20leaving%20the%20summary%20view%2C%20and%20allow%20the%20user%20to%20expand%2Fsee%20the%20details%20if%20interested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIssue%3A%3C%2FP%3E%3CP%3EIf%20it%20just%20hide%20all%20the%20rows%20(not%20filtering%20data%20on%20the%20tables%2C%20just%20hide%20rows)%2C%20the%20totals%20in%20the%20summary%20table%20goes%20zero.%20I%20want%20the%20totals%20still%20be%20shown%20(as%20per%20each%20table)%20regardless%20if%20the%20table%20(rows)%20is%20visible%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2712261%22%20slang%3D%22en-US%22%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-2712504%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Pivot%20Table%20in%20a%20sheet%20affects%20other%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2712504%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20found%20my%20answer.%3CBR%20%2F%3EInstead%20of%20referring%20to%20the%20cell%20that%20holds%20the%20total%20on%20the%20pivot%20tables%2C%20In%20my%20summary%20view%20I%20need%20to%20use%20and%20independent%20calculation%2C%20SUM()%20and%20use%20the%20full%20range.%20So%20on%20that%20case%20regardless%20the%20filter%20or%20hidden%20rows%2C%20the%20SUM%20will%20just%20calculate%20the%20total%20on%20its%20own.%3CBR%20%2F%3E%3CBR%20%2F%3ERegards%3C%2FLINGO-BODY%3E
New Contributor

Hi There

 

I have few Pivot Tables in a sheet, then I have their totals grouped in another "overall summary" table.

The idea is that you see this small 5-row table with totals and down in the sheet (on their own separate table) you have the breakdown of each total.

 

I want to be able to hide all those tables, leaving the summary view, and allow the user to expand/see the details if interested.

 

Issue:

If it just hide all the rows (not filtering data on the tables, just hide rows), the totals in the summary table goes zero. I want the totals still be shown (as per each table) regardless if the table (rows) is visible or not.

 

Any ideas?

 

Thanks! 

1 Reply
best response confirmed by fsaint (New Contributor)
Solution
Hi

Just found my answer.
Instead of referring to the cell that holds the total on the pivot tables, In my summary view I need to use and independent calculation, SUM() and use the full range. So on that case regardless the filter or hidden rows, the SUM will just calculate the total on its own.

Regards