link should evaluate to #ref error, returns value

[Pete M]

Hi,

In Excel 2002, I have several links to various cells in an external file.
The directory and file name can change so the link is originally formated as:

='X:\My Documents\Excel\conledgr\fyXX\[CNSLXXXX.xls]A'!$E$39

The fyxx and CNSLXXXX are not a valid directory or file name. Part of a
macro changes the fyxx and CNSLXXXX to a valid directory and file name.

The formula as entered should return the #REF error and does so in all
instances except one. That formula is returning a value.

How can that be if the directory and file name in the link are not valid?

The file containing the links noted is being open from the "X" network
drive. If I change the link to point to another drive the formula returns
the #REF error as expected.

This process of replacing the XXX's with valid directory and file names has
worked reliably in the past. Those links always displayed the #REF error
until a valid directory and file name replaced the XXX's.

Any idea why this is happening with the one link?

 

[Pete M]

I think i've figured out why the link is evaluating to a value when it should
return the #REF error.

When you enter a link to an invalid external file, Excel opens a dialog box
asking you to locate the file. If you cancel the dialog box the #REF error
is displayed. I believe instead of cancelling, OK was clicked instead and
the file highlighted was used for the invalid link.

I've been able to re-create this by highlighting different files and
clicking OK instead of cancel. That returns the value in that file for the
specified cell.

Knowing that, how do I "break" the link to that file chosen in error? The
link, as noted in the original post, contains an invalid directory and file
name yet now points to an actual directory and file name.

Attempting to "fix" the link, which reopens the locate file dialog box, and
chosing cancel retains the link to the original valid file chosen in error.


:

 

[Pete M]

To resolve this I created a new blank worksheet. That worksheet had a tab
named Sheet1. I pointed the link in question to that blank worksheet.

That link then returned the value 0.00 and the link changed to:

='X:\My Documents\Excel\conledgr\fyXX\[CNSLXXXX.xls]Sheet1'!$E$39

I changed the reference from Sheet1 back to A, as the original link had,
canceled the find file dialog box and the #REF error is now displayed as
expected.

The link no longer points to a valid file in error.


: