If you have not put the full path in the filename, Excel will assume
that the file is in the current directory and look there - if the file
is not in that directory then it is treated as a bad reference (hence
#REF error). Just copy the file into the current folder.

When the file is open it works okay because Excel can find the
reference.

Hope this helps.

Pete