Updating formulae that reference data in another workbook when the data workbook is closed

Hi. I have one main data spreadsheet with 65K+ lines of data of payments from clients. On another sheet in that workbook I have a report which shows, by way of formulae, total income data for top 50 clients by year, and then some other stuff, for one consultant.

I have duplicated that report sheet in another workbook so that the consultant can work with it, and she can input some additional data (e.g. last meeting with client date), but not have any chance of messing up the original data! Both workbooks are on a shared drive so the path to the data workbook is identical on my machine and on hers.

I update the data workbook once a month with exported data from our payment system. What I want is that the report workbook will update from the data workbook even when the data workbook is closed. My understanding is that this is possible.

The formulae I have used are (I think) properly formatted, but when I click F9 to refresh the report workbook, all the formulae immediately show #VALUE! and there is a pop-up which says "A value used in the formula is of the wrong data type." However when I have the data workbook open, the report workbook updates properly.

An example of the formulae I am using is as follows (they are quite complex, take a bit of time to calculate, but do work):
=SUMPRODUCT(SUMIFS('C:\Data\Mastercopy of all data\[Combined data.xlsx]data'!$J:$J,'C:\Data\Mastercopy of all data\[Combined data.xlsx]data'!$E:$E,$C13:$L13))

(This formula is essentially adding up the income column when the client name column is a specific value, as defined in 10 hidden columns in the report workbook).

Am I doing something wrong?

Thanks Tony. The data is 65,000 line (data since 2008), and that's added to each month (not sure by how many) - is it worth restricting it to for e.g. J1:J100,000 (future-proofing for some years at least)- will that make a difference? If does take a very long time to calculate each time.

Thanks, Tony. I have to re-gig it all soon, so will include this amendment then. I guess, as Excel has up to 1,048,576 rows, by using E:E I am asking it to check 1,048,576 rows. Using E1:E100000 that might effectively reduce the workload by a factor of about 10?
I may be overcomplicating this, but is there a way to express it as E1:Ex , where x is the number of rows populated?

I am not aware of a method to do that to link to a closed file.[/QUOTE]

I'va abandoned the idea of having the report in a different workbook as it won't work when the data book is closed with the formulae I am using, so the report will just be on another sheet in the same book - is there therefore a way to make E1:Ex , where x is the number of rows populated work?

(Thanks so much for all this help!)

Yes, I understand that, but is there a way of expressing 'all of column E, but only where there is data', without using 'E:E' (which will create a lof of work), or 'E:E100000' (to account for the fact that there are currently 65,000 lines, but it is being added to every month and will eventually exceed 100,000 lines, at which point the formulae will not work)? Does my question make sense?