Detecting invalid links

[elanus]

A quick question about links, probably with an obvious answer for those in the know.

I want to avoid a value being displayed if a link is not valid because the file does not exist. Is there an 'If Exists' command or similar?

I have up to ten individual spreadsheets all containing a field that I want to include in a single composite file. These spreadsheets are named 'file1.xls', 'file2.xls', etc.

The composite file that retrieves each of the fields from the individual files uses the formula ='c:\Excel\[file1.xls]Sheeta'!$E$64 to retrieve the field from the first file, ='c:\Excel\[file2.xls]Sheeta'!$E$64 to retrieve the field from the second file, and so on. So far so good; all fields are retrieved from their separate files and I can print a report from the composite spreadsheet that shows all their values.

Now, sometimes some of the files will be missing. When I open the composite file to print the new report, I get the normal warning 'This workbook contains links to other data sources' and I am offered the option to update or not. I select 'Update'.

If some files are not present, I get the further warning 'This workbook contains one or more links that cannot be updated. To open as is, click Continue'. I click Continue.

However, when I look at the composite spreadsheet, the new values from the files that exist are displayed (great), but the values from the missing files still show whatever value was previously in them from the last update.

I want to be able to see the current state of play; new values from files that exist and some kind of null value, or zero, or error message from the files that don't exist.

Any advice would be appreciated.

 

[Andrew Poulsom]

You can use a formula like this:

=IF(ISERROR('c:\Excel\[file1.xls]Sheeta'!$E$64),"",'c:\Excel\[file1.xls]Sheeta'!$E$64)

 

[elanus]

This formula doesn't achieve what I need. It has the same result as my initial formula. The field is found if the file is present, but if the file is not present, the link cannot be fulfilled and the previous value, which is no longer valid, remains.