#REF! does not show

[JL]

Hi,

Sorry for not being clear before. This is what I was
trying to do. I have a formula that will look into
another closed file (excel file) and determined what to do
based on that value.

For example:
=IF('C:\Test\[Sheet1.xls]Sheet1'!$B$6='C:\Test\[Sheet1.xls]
Sheet1'!$G$11,0,INT('C:\Test\[Sheet1.xls]Sheet1'!
$G$52+0.5))

The problem is that when file "C:\Test\Sheet1.xls" don't
even exist. It will still retained the value from the
last time. I am expecting that when
file "C:\Test\Sheet1.xls" does not exist. It will give me
#REF! error. Not the value that was there before.

I hope this make sense.

Thank for the help in advance.

 

[Norman Harker]

Hi JL!

Agreed as not very user friendly where the link is changed whilst the
referring workbook is open. Even a full recalculation doesn't change
the value from the one that existed when the target workbook was
properly located.

Two approaches.

Close and re-open the workbook will reveal a link problem and allow
correction.
Or
Edit > Links will reveal the problem and allow correction.

But perhaps it might have been more appropriate for the cell to
default to #REF! when that link is broken.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 6th August 2003: Bolivia
(Independence Day), Bulgaria (The Transfiguration), Jamaica
(Independence Day), United Arab Emirates (Accession of H.H. Sheikh
Zayed). Observances: Hiroshima Day (08:15 Local Time 6th August 1945),
Hiroshima Peace Ceremony (Shinto), The Transfiguration (Christianity).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.