Detecting invalid links

elanus

New Member
Joined
Jan 22, 2004
Messages
2
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use a formula like this:

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

elanus

New Member
Joined
Jan 22, 2004
Messages
2
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,692
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top