Links keep failing in shared document

[fabrecass]

Hi guys,

I have a tracker document that is shared, certain cells are linked to a number of different status sheet documents, this is all saved on the same network drive. However, when I open the tracker document some links (failing links are always from the same document) are returning "NO VALUE". If I open the document it's linked to it always fixes itself.

Anyone have an idea of what's going on?

[Jamesr571]

When prompted to update the link when opening the sheet what are you selecting?

I use a similar spreadsheet and also choose to not update the link and it works fine.

[fabrecass]

I've set it to not update and never ask in edit/links. Thought that'd fix it but #REF! and #VALUE! values are returned in places.

[6StringJazzer]

Are the links just direct references, like

='[H:\Files\Folder\Status.xls]Sheet1'!A1

or is there use of the INDIRECT function, or any other function in the formulas? Some functions, like INDIRECT, require the referenced workbook to be open, otherwise they return a #REF! error.


You said that if you open the referenced file, it fixes itself, so what I'm about to describe is not your problem but it bears including:

When you get #REF! errors, is it just in the cell, or is your formula actually changed to

='[H:\Files\Folder\Status.xls]Sheet1'!#REF!

If your formula looks like this, it means that the row or column in the referenced sheet was deleted.

[fabrecass]

Sorry i should have gone into more detail, no they're COUNTA functions, would the rule for the INDIRECT function still apply?

[fabrecass]

no one have any idea?

[6StringJazzer]

COUNTA should not cause this problem. COUNTIF will return a #VALUE! error attempting to update a link to an external file, but COUNTA seems to work fine.

I am not in a networked environment so I am not able to explore this across networked drives; that could certainly be part of the issue.

You said that the errors occur always in the same document. Do the formulas referring to that document contain functions that your other formulas don't?

Regardless, you are probably running into behavior that Excel provides by design.

Do direct links fail also, or just ones with functions? One workaround might be to put additional worksheets in your workbook that directly reference the data in the other workbook, and then have your functions and formulas point to that data.

[fabrecass]

my mistake, i think it's the COUNTIF functions returning #VALUE!, however, i've set it to never update/never ask via edit/links, shouldn't this counteract the problem? the work around you suggested should work, it'd just be quite time consuming on my part.

The errors occur on the central document, many many different status sheets are linked to this document. Direct links return a #REF! value when they fail, but yes, they are failing too.

[fabrecass]

can i bump this up?