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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|