External Links #REF! Errors

[mark5767]

This is starting to get a little frustrating.

I'm using Excel 2007 and have what I believe to be very similar workbooks with the same external link settings in Options and Trust Center.

However, one workbook (#1) opens without returning #REF! for the external links which is what I want.

The other workbook (#2) opens and immediately returns #REF! for most of the external links which forces the user to open linked file to clear the reference errors. The users don't like this and tend to blame me for causing them problems.

Does anyone have thoughts on why this might be occuring? Thank you!

P.S. This is my second and last question for today kind people

 

[Derek Brown]

Can you give an example of the formula that is giving the #REF error, please.

 

[mark5767]

Thanks for your response Derek. Below should be an example of the types of references the files are using. They are all conditional index/match formulas referencing a source Excel file where cost tables have been queried from a manufacturing database.

=IF($A7=$D$5,INDEX('[BOM Cost Table Example.xls]Cost Table'!$A$2:$G$200,MATCH('600007'!$A7,'[BOM Cost Table Example.xls]Cost Table'!$A$2:$A$200,FALSE)+$B7,7),"")

The formulas are pretty identical in the two workbooks, although they reference different source files. For example, one workbook would reference [BOM Cost Table Example], while the other would reference [BOM Cost Table Example 2]. So it may be possible the source files are saved with different settings I suppose, but I really don't think so.

These workbooks are pretty massive so it's a little hard to be super specific with examples, but hoping my representation gives you enough information to offer a suggestion. Thank again!

 

[shg]

=IF($A7=$D$5, INDEX('[BOM Cost Table Example.xls]Cost Table'!$A$2:$G$200, 
 MATCH('600007'!$A7,'[BOM Cost Table Example.xls]Cost Table'!$A$2:$A$200, FALSE)[COLOR=red] + $B7[/COLOR], 7), "")

Perhaps the value in B7 results in the row index going beyond the extents of the table?

Have you tried to use the Evaluate Formula button to see what's happening?

 

[mark5767]

The links always work fine, but one workbook requires that the source file be opened to refresh links while the other just shows the un-refreshed values (until you actually refresh the links).

So I would like the first workbook to just show the un-refreshed values like the second one does.

I will probably need to look at the actual files again when in the office during the week some time and then post a more specific question here. I thought there may be a general conceptual issue here, but it's probably more detailed than that.

Thanks for your responses, I will get to the bottom of this one sooner or later!

 

[shg]

Well, there is a general conceptual issue; functions that REQUIRE a range as an argument (e.g., INDIRECT, RANK, SUMIF, ...) don't work with closed workbooks. That doesn't apply to the formula you posted, though.

 

[Derek Brown]

Perhaps this may help:
http://support.microsoft.com/kb/925893