A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#REF! to linked cells when linked file is missing



 
 
Thread Tools Display Modes
  #1  
Old March 31st, 2004, 02:46 AM
kiln
external usenet poster
 
Posts: n/a
Default #REF! to linked cells when linked file is missing

On a spreadsheet a college has created, which contains links to another
excel file, cells that are pointing to null values in the remote file
are all showing #REF! when the remote file is not available. In my own
tests, keeping Zero Values, Update Remote References, and Save External
Link Values checked, null value cells in the remote file usually show as
zero. So I'm trying to figure out why some of the ones he has created
show #REF!.

As an experiment, I had my college create a new sheet in the same
workbook, with a couple of links to null cells in a file I don't have,
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?
  #2  
Old March 31st, 2004, 08:03 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default #REF! to linked cells when linked file is missing

[snip]
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?


Yes this is a possible scenario

Frank

  #3  
Old March 31st, 2004, 08:13 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default #REF! to linked cells when linked file is missing

Kiln wrote:
So I'm trying to figure out why some of the ones he has created
show #REF!


What do you see in the formula bar when you look at those cells?
I would imagine that either the referenced sheet name has been changed
or the link has been changed to a different source workbook which
doesn't contain the relevant sheets or range names.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #4  
Old March 31st, 2004, 10:18 PM
kiln
external usenet poster
 
Posts: n/a
Default #REF! to linked cells when linked file is missing

Hi and thanks to you both

='D:\office\leb\docs\[NewData.xls]NGL'!B8

is what you see in the formula bar. But I'm not sure if you understood
the ground level issue here - I don't have NewData.xls at all, and never
will. My college has that file, and sends me the file with the link
above that fails with #REF!

What I find so puzzling is that if he makes a brand new link to a file
that I also don't have, null values show as 0 not #REF!. Thus the
thought that at some point when he didn't have the linked-to file, and
he updated, he stored those #REF! errors...but he's sent me many
editions of the file since this error cropped up, and when he looks at
it on his end the values are all zero'd out as I'd expect. So again I'd
expect the lastest ones he's sending me would have stored the zero's.

In article , says...
Kiln wrote:
So I'm trying to figure out why some of the ones he has created
show #REF!


What do you see in the formula bar when you look at those cells?
I would imagine that either the referenced sheet name has been changed
or the link has been changed to a different source workbook which
doesn't contain the relevant sheets or range names.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.