Hi,
I have a report in which all the cells are linked to a pivot table in another tab using the GETPIVOT formula. This pivot table tab alone is 50MB.
Users can click on different buttons in the report to change data based on different regions, quarters, etc. I cannot send a 50MB file to users because it takes a long time to open. So I decided to load my pivot table file to a remote sharepoint and linked all the cells to this sharepoint file. Although the file size of the report became 10 MB, when I clicked on a button the report would have the REF sign. So basically, the file was unable to get data from the sharepoint file. However, the file works if the sharepoint file is open.
Checked online and it seemed like the GetPivot formula was the issue, however, I do not want to go back and relink all the cells in my report...So now I need a way to have both files open without sending 2 files to the user. I decided to create a macro that will automatically download and open the file from sharepoint when the user opens the report. While this worked the first time I ran the macro, it did not work once I closed all files and opened them again. All the cells were linked to the Sharepoint file instead of the downloaded sharepoint file.
Does anyone have a way to keep my cells linked, reduce my file size, and have the data update when a button is clicked? Would appreciate your help!
I have a report in which all the cells are linked to a pivot table in another tab using the GETPIVOT formula. This pivot table tab alone is 50MB.
Users can click on different buttons in the report to change data based on different regions, quarters, etc. I cannot send a 50MB file to users because it takes a long time to open. So I decided to load my pivot table file to a remote sharepoint and linked all the cells to this sharepoint file. Although the file size of the report became 10 MB, when I clicked on a button the report would have the REF sign. So basically, the file was unable to get data from the sharepoint file. However, the file works if the sharepoint file is open.
Checked online and it seemed like the GetPivot formula was the issue, however, I do not want to go back and relink all the cells in my report...So now I need a way to have both files open without sending 2 files to the user. I decided to create a macro that will automatically download and open the file from sharepoint when the user opens the report. While this worked the first time I ran the macro, it did not work once I closed all files and opened them again. All the cells were linked to the Sharepoint file instead of the downloaded sharepoint file.
Does anyone have a way to keep my cells linked, reduce my file size, and have the data update when a button is clicked? Would appreciate your help!
