#Value and #Ref, links between multiple file not updating

[CA_User]

I have a series of excel files linked to other files. Sometimes the workbook opens with #value, or #REF but the links are not broken... The value ONLY update and show up as numbers (vs errors) when I have the correspnding source file open.

I tried to update the links via "edit links" and Update values. Sometimes when I do that, cells with actual numbers turn into the errors #REF or #value. I then have to open the workbook to get rid of them.

Please help!

 

[Oaktree]

Certain functions (such as INDIRECT) require the referenced file to be open. What's the formula that is giving you #REF!'s?

 

[CA_User]

All the formulas are direct links "= file path and cell name
There are some vlookups but there are no fancy formulas....

 

[beerw0lf]

Ca _User
Did you ever get this isue resolved?

 

[CA_User]

No. i have not receied an answer to this

 

[beerw0lf]

I'm having the same issue. I am very interested in a solution as I deal with several files with several worksheets in each. I have links to 3 different files. The links to 2 of the files seem to update fine but I need to open the third to have the cells and formulars update.


Thanks

 

[Oaktree]

Please post the formula that's giving you a #REF! error when the referenced file is closed.

 

[CA_User]

=IF(EF$5="Actual",SUMIF('[2007 BU Actuals.xls]0107USD'!$A:$A,$A11,'[2007 BU Actuals.xls]0107USD'!$G:$G)/1000,ET11)

 

[Oaktree]

There is nothing inherent in that formula that would cause you to get a #REF! error.

Have you checked your referenced cells to see if any of them have #REF! errors? If there was even one #REF error in '[2007 BU Actuals.xls]0107USD'!$A:$A or '[2007 BU Actuals.xls]0107USD'!$G:$G, you'd get a #REF! error returned.

 

[CA_User]

This particular formula i listed previously returns #value and refreshes to a number when I open the source file. The source files are all hard coded.