Each month I create a report with over 100 summary tabs that are referencing 22 data tabs. All tabs are in the same file.
The report works great! It is easily manipulated for all of the one-off requests I receive and the information is well presented and useful to my many customers. My customers are thrilled with the spead and accuracy with which I can provide new and varied reporting.
The problem I am having is that the file is too large. Those customers who want the whole file are having to wait too long for it to open...and some can't always open it.
Other customers only receive PDF files of the tabs that are important to them...so, no problem there.
I've uploaded an example of the report structure into dropbox. Here
This file has an example of the data (not actual data) and a simplified example report. The report includes the main formula I'm using to pull my data and a general idea of the format.
I've never used dropbox before...will you be able to see the formulas?
Just in case you can't...here is an example of the main formula used to pull data into the summaries:
=SUM(OFFSET(Data!$A$1,MATCH($A11,Data!$A:$A,0)-1,MATCH(C$2,Data!$1:$1,0)-1+IF(C$2="Actual",MATCH(C$3,Data!$2:$2,0)-2,MATCH(C$3,Data!$I$2:$II$2,0)-1),1,C$5))
I'm using offset formulas to find the data I need based on Row Label, Column (Find Version, then find Beginning Period), height = 1, width = # of periods.
The full report is over 21,000KB. If I can find a formula that will work with the data tabs in a separate file, it looks like the report size will fall by about 6,000KB.
Can you help me reduce the size of this file?
- Either by building a formula that will work when the data is in a separate workbook...and that workbook is not open.
- OR, taking some other approach. I.E. Is VBA a good option? If so, how do you recommend getting started with that?
A few things to keep in mind:
1) My customers are very picky about the format. Any changes I make needs to be presented in the agreed upon format.
2) Several of my customers want to see the data tabs. Even if these are not in the file, I will need to make them available.
3) I work in a fast paced environment and need to be able to manipulate reporting/summaries to present the data differently, quickly...and frequently.
4) The current file accomplishes all 3 of these things, but is too big.
Thank you so much for your help!
Amy
The report works great! It is easily manipulated for all of the one-off requests I receive and the information is well presented and useful to my many customers. My customers are thrilled with the spead and accuracy with which I can provide new and varied reporting.
The problem I am having is that the file is too large. Those customers who want the whole file are having to wait too long for it to open...and some can't always open it.
Other customers only receive PDF files of the tabs that are important to them...so, no problem there.
I've uploaded an example of the report structure into dropbox. Here
This file has an example of the data (not actual data) and a simplified example report. The report includes the main formula I'm using to pull my data and a general idea of the format.
I've never used dropbox before...will you be able to see the formulas?
Just in case you can't...here is an example of the main formula used to pull data into the summaries:
=SUM(OFFSET(Data!$A$1,MATCH($A11,Data!$A:$A,0)-1,MATCH(C$2,Data!$1:$1,0)-1+IF(C$2="Actual",MATCH(C$3,Data!$2:$2,0)-2,MATCH(C$3,Data!$I$2:$II$2,0)-1),1,C$5))
I'm using offset formulas to find the data I need based on Row Label, Column (Find Version, then find Beginning Period), height = 1, width = # of periods.
The full report is over 21,000KB. If I can find a formula that will work with the data tabs in a separate file, it looks like the report size will fall by about 6,000KB.
Can you help me reduce the size of this file?
- Either by building a formula that will work when the data is in a separate workbook...and that workbook is not open.
- OR, taking some other approach. I.E. Is VBA a good option? If so, how do you recommend getting started with that?
A few things to keep in mind:
1) My customers are very picky about the format. Any changes I make needs to be presented in the agreed upon format.
2) Several of my customers want to see the data tabs. Even if these are not in the file, I will need to make them available.
3) I work in a fast paced environment and need to be able to manipulate reporting/summaries to present the data differently, quickly...and frequently.
4) The current file accomplishes all 3 of these things, but is too big.
Thank you so much for your help!
Amy
