Export colapsable pivot table to Power Point

[JCamara]

Hello, Comrades!

I've designed a spreadsheet to users input data so it spill some charts. Every month the guy who hired me will grab the charts and send them via email abroad the clients. The main file is a Powerpoint (smh)...

That was working fine until someday someone had the brilliant idea to add a pivot-table, with collapsable rows as "mandatory information" for the presentation. fml.

Bottom of mind:
I need to export a "colapsable" pivot table, but protecting the original content. Final user cannot have access to where the pivot table is reading data. That renders hyperlinks useless.


I know Powerpoint has its limitations, so what I had in mind is to create a link in one slide that opens an Excel file containing only the table. The problem is "mid-user" (who I work for, and who will make the presentation grabbing only the charts), knows squat about Excel. That being said, I need a simple way to transfer only text and the colapsable rows (or grouped rows, what ever). I tried to copy, and inside "paste special", tried every single type, hoping that the colapsable rows would be imported. Needless to say i had no success.

So, as the life is dark and full of terrors, I'm wondering how to approach this hardcoding quest I have beyond my petty destiny:

option_1) a macro that copies every relevant row of the main table (only three actually) into a new file, and then generates a pivot table the way I want.
option_2) a macro that fully expands original pivot table, then copy-paste it as text into a new file, and then starts to identify what is a main subject (and shall not be collapsed) and what rows need to be grouped


Is there any other way to export the table as i want? Do i really have to use a macro for it? If so, can someone enlighten me with a procedure simpler than what i pictured?

Thanks in advance!

 

[SpillerBD]

Collapsible Pivot in PowerPoint. No.
You could have an Excel file that is only the Pivot Table. After you copy the Worksheet to a new Workbook, go into the Pivot Table Options and un-select "Save Source data with file"
Same for Pivot Chart.
During display of Excel, the formula bar can be hidden as well as the Ribbon.

(The Power-BI for Desktop has some advantages to look at but you're probably to short of time to investigate for this specific need.)

Fire the mid-user and get a high-user

 

[JCamara]

Thanks, mate! I didnt knew about this option!