How to handle reports with the help of functions where data linked to multiple files

[kundanlal]

Many of us may be using functions like sumproduct (works on closed database, therefore preferred), sumif etc. to generate various type of outputs.

I have been using a Sales Report which uses input database having three sheets with data ranging from A1 TO CB4000 (Similar cell range in all three sheets). This data is available in different monthly files say Sales April, Sales May etc.total 12 files for particular year.

While calculating Sales report for particular Sales code, I am using sumproduct function (as there is no need to keep all files open at same time as and when I open this sales report file) with some criteria. While going forward, Sales report would display all months output. I need to maintain Month wise sales one after other.

When I open this report file it calculates for all cells every time, excel takes very long time to open.

I tried to copy all database in this report file so as to avoid opening of multiple files, but this is getting bulky and not feasible.

How to handle such situation so that the Report File would open and calculates fast.

[candybg]

I work in accounting and work with similar files every month. The approach I found that works best is to keep all data in a single file. I copy and paste each month's data into a master file that has a new Col A (that is, the data is pasted started in Col B). I reserve Col A to enter this month's date for the corresponding lines.

This data is organized as a DATA TABLE (one header row, no blank rows, etc)

Then I use various Pivot Tables to present my reports, stored on additional worksheets.

Each month new data is pasted at the bottom of the DATA TABLE, date typed/copied down Col A, then I just REFRESH ALL and all pivot tables refresh to show the new data. Takes only about 2 min per month once set up.

Hope this helps!

[FDibbins]

Can you share spme of your formulas? SP can become very resource-heavy and slow your file down if used over large ranges

[kundanlal]

SUMPRODUCT(('C:\Documents and Settings\AB\Desktop\Office\Industry Distributor Data\[Distributor Data-February 2016-RoMG.xls]GS Feb 16'!$M$4:$M$3334)*('C:\Documents and Settings\AB\Desktop\Office\Industry Distributor Data\[Distributor Data-February 2016-RoMG.xls]GS Feb 16'!$A$4:$A$3334=$A$1))/100

This is being used to fetch details of a particular agent which is at A1 and the report runs for around 12-15 heading stored vertically and for each Months which are stored horizontally (april to march). While I continue to show details for all months, for each and every heading vertically and for all months, (which is linked to that particular month), formula gets calculated when I open a file. Of course, when it gets opened and I change agent name, the output is displayed fast.