#REF on Links to Named Range in External Workbook

[stuart_bisset]

Hi there everyone,

I have a workbook which contains links to named ranges that are in
external workbooks stored on the server. When the external workbook is
open the cells show the correct values. When the external workbooks
are closed the cells show #REF. The formulae are obviously correct so
why can excel only read from files that are open? Can anyone explain
this? Also - a small additional query - why is it that when excel
links to an external file it drops the square brackets round the file
name & path if the link is to a named range. The square brackets
appear if the link is to a cell address?

Thanks in Advance

Stuart

 

[Dave Peterson]

There are worksheet functions that don't work with closed files.

For instance:
=indirect(), =sumif(), =countif()

You may want to post your formula. There may be alternatives.

I think it has more to do with if the name is a global (workbook level) or a
local (worksheet level) name. And whether excel wants/has to include that
worksheet name.