MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Linking problem

Posted by Greg Marshall on August 02, 2001 3:17 PM

Here is my issue: When I create a file with external links to other files and send that file by email or another user attempts to open the file from a shared server, they select "no" when prompted whether they want to update the links to the external files. When they decline to update links, all formulas with external references return "#value!". I recently changed PC's and never had this issue before now - typically the last populated dataset remains present in addition to the underlying formulas. Any ideas?? Thanks in advance for any suggestions or ideas on how I might address this problem. Please let me know if I have not articulated sufficiently and I'll try to explain further.

Posted by Robb on August 03, 2001 8:35 PM

If they have access to the linked file and want
up to date data, they should elect to update
the links. Otherwise you need to remove/replace
the links before you send the workbook.

In the event that the recipient needs to only
see the values that are current when you send
the workbook then, before you send the workbook,
select each sheet, select all cells in the sheet
(or at least all the used ones),copy, edit/pastespecial
and then select to paste values. This will
replace all the formulae/links with the current

If you need to remove only selected links, post
a reply.

Posted by Greg Marshall on August 06, 2001 6:34 PM

Thanks Robb for your response!

I have gone the "paste values" route to get around the problem, but I believe that it should work for the recipient to decline to update links and still pull back the last result returned by all formulas with external links on the page.

Anyway, thanks for your response and I may have no choice but to do the paste values operation. The down side is that it makes the sheet impossible to audit and sacrifices comprehension on the part of the user.