Help!! Need to update links from a closed workbook !

[alwizardus]

I am using sumif function to capture data linked to a subordinate workbook.

Thing is I have to open the subordinate workbook all the time for links to update.

Is there not a way to use formulas that do not require other source workbooks to be opened.

I tried Sumproduct, but the data i am pulling is large and CPU cannot handle it - slows computer. Sumif is much quicker.

Is there an Excel Add-in functions that can help with updating cell values from closed workbooks??

Or, is there an affiliate program that can help??

Thanks in advance !!

 

[Dannyh1]

Hi,

Download MOREFUNC,

then use SUMIF & INDIRECT.EXT combination.

 

[alwizardus]

Thanks !

Can you illustrate the formula in detail or give me an example?

My Excel skills are average.

Again, thanks in advance !

 

[Dannyh1]

Hi an example would be

=SUMIF(INDIRECT.EXT("'C:\Danny\[book1.xls]Sheet1'!$A:$A"),$A1,INDIRECT.EXT("'C:\Danny\[book1.xls]Sheet1'!$B:$B"))

Obviously you could have your file reference (fixed or variable) in another cell e.g. C2 = C:\Danny\[book1.xls]Sheet1'! then it would be :

=SUMIF(INDIRECT.EXT("'"&C2&"$A:$A"),$A1,(INDIRECT.EXT("'"&C2&"$B:$B")))