Reference to massive table in another workbook

Hi all!

I got a tricky problem. Recently I got a huge workbook on my hands, 80MB of data. It basically consists of two parts, financial indata in 12 sheets, one per month with actuals, and maybe 10 presentation sheets where all the actuals are summed up according to certain business rules.

When I got this in my knee I though I should make it more manageble by breaking it up in pieces. One "database" file with the actuals, and 10 to six presentation workbooks that would all refere to the same "database". The presentation files once devided are still pretty heavy reaching up to 20MB because of loads of stupid formatting I am not in charge of.

The suming up in the presentation workbooks is done by sumifs(). Now I have learnt the hard way, that this formula only works when the database workbook is open. So now to my dilemma, I cant use macros, so I se one of two ways to solve it.

1. I swap the sumifs for sumproduct. Which will be cumbersome in it self as we are talking of loads of colums and thousends of rows with sumifs...

2. Formatting the database as table, then create a data conection to it from the presentation workbook, and keep sumifs but refering to the imported table.

From the start I have been keen on formatting the database sheets, now seperate file, as tables,

But I have also realized that I cannot refer to formal tables with structured reference if the other workbook is closed, can anyone confirm this?? So then I have to data import the database tables. That is cool with me but will I not then increase the size of presentation workbooks again? The thing I tried to avoid in the first place.

Size is not really a matter per se, but rather performance and reliability. I have 8GB ram and i7 inte core CPU, but it is on its knees whenever calculating the files, and crashes alot.

So my question here is also regarding what will have better performance, sumproduct to another workbook, or importing the table and using sumifs? Oh database is 20Mb on its own.

Thanks in advance, any answer is apreciated

Can't you use pivot tables?

Thanks for answereing.

I kind of though that SUMPRODUCT wasn't going to be the most efficient formula.

But I made a test with a reference like this:

=SUMIFS('C:\temp\[Book.xlsx]Sheet'!$A$2:$A$4;'C:\temp\[Book.xlsx]Sheet'!$C$2:$C$4;'C:\temp\[Book.xlsx]Sheet'!$E$2)

And I do get an value error as soon as I recalculate with Book.xlsx closed. Am I missing something?

Cheers

Originally Posted by Kyle123

Can't you use pivot tables?

It could have been a possibility earlier on, before I was involved, but right now the formatting of the presentation sheets is not suitable for a pivot. And there are to many rules. But using a table in the database workbook as an external datasource I guess does the same trick.

Or did you have something else in mind?

Originally Posted by Spencer101

SUMIFS does work on closed workbooks, but not if you're using dynamic named ranges.

I am not so sure about that at 58:37

http://www.youtube.com/watch?v=pHFPnHdMLoY#