Links not updating unless Source Open

[BrandyExcel]

I have a file that has a number of external links. When I open the file and choose to update links, all of the links come back with a #Value. If I go to Data-Edit links and do a "check source", all of the sources are ok. However, the values will only stay in my spreadsheet while the source is open. This is troublesome because the links refer to 20 different source documents, and I don't want my users to have to open them all (hence, the linked file). I have tried playing with the different calculate options to do with links to no avail. Does anyone have a suggestion?

 

[shg]

Not all Excel functions work with closed workbooks. What are some of the ones that fail?

 

[BrandyExcel]

it's a sumif function, summarizing item level data into departments for my users.

 

[shg]

Thant's one of the functions that won't work. Others include COUNTBLANK, COUNTIF(S), INDIRECT, OFFSET, and RANK -- in general, functions that require ranges as arguments.

You could replace your SUMIF with a SUMPRODUCT.

 

[BrandyExcel]

thanks a bunch!

 

[shg]

You're welcome, good luck.

 

[Nomarch]

Does the list of functions that won't work (range functions) include IF and OR functions? I'm in the same boat as BrandyExcel is in that I'm trying to have a formula that searches for a particular entry based on multiple sets of criteria. (Hence the need for the OR function)

Thank you,
Luke