Suddenly, many #REF! to linked files

[kiln]

I hope someone can explain this to me. I'm not all that familiar with
Excel.

A college sends me periodic updates to an excel file (merger.xls). That
file has some cells with external links in it, like

='D:\docs\[newdata.xls]DetailData'!B10

I don't have the mydata.xls file that is pointed to. Ordinarily, when
I've opened past editions of the merger.xls file, I see the "Update
Links?" dialog, and I tell it not to. The data value, the one retreived
by the link above, was visible (returned a value of 1000 say). But in
the latest edition of the merger.xls file, most of those linked cells
show #REF!, but not all; some show the numeric value as it was.

Can anyone tell me what is going on here? I'd sure appreciate it.

My best guess is that my college moved some cells in his linked file,
newdata.xls, breaking something somewhere, and the breakage is reflected
in the file he sent me. I guess Excel stores the last value it had
pulled out of linked files, so it would have saved the broken ref too?

 

[Dick Kusleika]

kiln

Go to Tools - Options - Edit tab and make sure that "Ask to update external
links" is checked.

 

[kiln]

kiln

Go to Tools - Options - Edit tab and make sure that "Ask to update external
links" is checked.

Hi, it is. The problem seems to be occurring for all nulls value cells.
It seems that the collegue removed a bunch of zeros and the null values
are the ones that are breaking the refs. Any way around this?

 

[Dick Kusleika]

kiln

I think I may have answered the wrong question. For some reason I thought
the question was that it wasn't asking you to update links.

I think you're right that something is happening on the other end that is
breaking those links, but I can't think of a situation where empty cells
instead of zeros would create a #REF. I'm sure there is one. Do the
formulas look like the one in your first post, or do they show the error
message somewhere in the formula?

Any chance you can get a look at "newdata.xls"?

 

[kiln]

Hi and thanks for staying with this...

I can't send it unfortunately. But, I had my college create a new sheet
in the same workbook, with a couple of links to null cells, and they
show as...zeros just like I'd expect.

I wonder if something could happen like...someone at some time opened
newdata.xls when the linked to file was not available, and when prompted
told excel to update links, and all those cells refs were missing in
action... and were saved that way. Is that a possible scenario? Or, can
an excel file become corrupted?

 

[natei6]

Hi
I have had this happen when I used the space bar to delete value
instead of the delete key.

All the best

Nathan Sargean

 

[Dick Kusleika]

I can't send it unfortunately.

But can you look at it? You can have a ref error and when you look at your
formula, it can look like this

=VLOOKUP(#REF!,A1:B100...)

where the broken link is shown right in the formala. But you can also have
a situation where your formula looks fine, but the cell that is referenced
has an error. I was just wondering which of those described your situation.

I wonder if something could happen like...someone at some time opened
newdata.xls when the linked to file was not available, and when prompted
told excel to update links, and all those cells refs were missing in
action... and were saved that way. Is that a possible scenario? Or, can
an excel file become corrupted?

All of those are possible. The corruption is not probable though. You
might try posting this question to microsoft.public.excel.links and see if
Bill Manville has an idea (he's a regular in that group)

 

[kiln]

Hi

The formula does not show the #REF! inline; it's only shown as a result,
a product, whatever it's called.

OK, I'll try over in links. Thanks for your attention!

 

[Dick Kusleika]

kiln

Okay, that means that the error is in the other workbook, not in the link
between the two. The link is bringing over the correct value, that value
just happens to be an error.

You colleague should be seeing the errors on his end too. Someone deleted a
row, or something, in a workbook and it's propagating through all these
links. That's my guess anyway.