#N/A in linked cell

[Guest]

I have a destination workbook with links to multiple source workbooks. The
majority of links work correctly in the destination workbook to display
information that is in the cells of the source workbooks, even when the
source workbooks are closed. However, there are several cells in the
destination workbook that return #N/A from the linked source workbook if the
source workbook is closed. If I open the source workbook the cells display
the correct values. Why do the linked cells in the destination workbook
display correctly in some cells and return #N/A in others? How can change it?
BTW, the linked source workbooks where I'm having the problem contain text
values.

 

[Guest]

Some XL functions don't work if linked to closed workbooks.

Best practice is to avoid links to external workbooks, for this reason.

Dave

 

[Guest]

There are some functions that only work across workbooks when both are open.
I don't know that there is a list of them.

Need to see what types of formulas are giving you troubles. There are
usually work-arounds.

 

[GRM via OfficeKB.com]

You must determine the reason from the source files. As an example, #NA is
returned when you use the vlookup function and cannot find a match. The
solution here would be to add the item to the lookup table, or include the
condition in the lookup formula - in this case you would use ISNA ie if the
formula returns #NA put 0 or "" if text is desired.

 

[Dave Peterson]

Some formulas don't work with closed workbooks. (As others have told you.)

But lots of times, there are replacement formulas that do work with closed
workbooks.

=sumif(), =countif() can be replaced with =sumproduct().

=offset() might be able to be replaced with =index().

(I got #value! errors trying to use =sumif() and =countif() with a closed
workbook.)

You may want to share the formula you're using. Maybe someone can come up with
an alternative.