Efficiency Problem

This is a long/tough one, so please bear with me. I will strive to be as clear as possible.

I have created a workbook that does everything I want it to, but the problem is that it takes nearly 2 hours to re-calculate all of the formulae! I am hoping someone will be able to point out a few ways to make my formulae/layout more efficient.

Details:
Each month I export data fom a database into my workbook. It consists of 31 columns; each one containing a different ID or metric. Each row is an entry in the database. These entries are collected from 46 different sources each month, and each source can have multiple entries, as different metrics are collected at different points during the month. This leads to a sparce matrix of responses.

One of the columns relates to the source. The sources are from a fixed list (ID1, ID2, ..., ID46) There is also a column relating to the corresponding month (May-11, June-11, ..., Dec-12)

From this sparse matrix I wish to feed 46 spreadsheets, one for each source. I want to have the list of metrics in column A and the list of months across row 1 and then call on the export sheet to fill in the corresponding data.

Method:
Currently I use the following formula:

{=INDEX(EXPORTDATA!$A:$AE,(MATCH($A$1&B$1,EXPORTDATA!$B:$B&EXPORTDATA!$AC:$AC,0)),XXX)}

where: EXPORTDATA!$B:$B is the column relating to the source
and: EXPORTDATA!$AC:$AC is the column relating to the month
XXX relates to the column I wish to pull data from

This formula works but it takes a very long time to calculate.

I want it so that all I need to do each month is import the data and then all of the feilds automatically fill in. Since I wish to have a 2 year period, I get #N/A for most of my feilds, since there is no data present yet for the months in question. I simply use a conditional formatting to change the font of any errors to white so these errors are invisible.

I then also want to have a TOTALS spreadsheet, fed by the 46 source sheets. Since there are so many #N/As in my workbook, simply summing the values does not work. I also cannot use a SUMIF formula due to the multiple sheets.

Method:
To get around this I use a very messy formula that utilises the IFERROR formula, wrapped up in an if statement to ensure any months that don't have data remain blank.

=IF(SUM(IFERROR(ID1!E9,0),IFERROR(ID2!E9,0), ... ,IFERROR(ID46!E9,0))=0,"",SUM(IFERROR(ID1!E9,0),IFERROR(ID2!E9,0), ... ,IFERROR(ID46!E9,0)))

This formula, again, works but slows the process down hugely when filled across and down all months and metrics.

Any suggestion are more than welcome. Even for reading all the way through this, I thank you!

Hi Marvin,

Thanks for your response. Unfortunately I am using Excel 2007 so I would not be able to use PowerPivots.

I have screen updating and automatic calculation turned off when I am playing around with the formulae, but when it comes to recalculating the feilds after an import there is no avoiding the 2 hour wait!

I was thinking the same as your point about trying to put everything on the same spreadsheet but there is so much data it would be very confusing for someone to find what they're looking for. I will try brainstorming new formulae to avoid the arrays I have. These arrays are used more than any othe formula, so replacing them would undoubtedly help.

Thanks again!