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  

Error message #N/A appears in linked cells



 
 
Thread Tools Display Modes
  #1  
Old July 16th, 2008, 08:30 PM posted to microsoft.public.excel.links
Jake
external usenet poster
 
Posts: 319
Default Error message #N/A appears in linked cells

Hello,
I'm using Excel 2003 and have files with lots of links. Cells are linked to
an external workbook. The cells in the same column have inconsistent link
results. Some cells display #N/A and some cells are fine. The formulas are
the same, except they refer to a cell in a different row. When I open the
supporting sheet the cells update fine and the #N/A disappears and I get the
source data. When I close the supporting workbook the #N/A returns. The
cells are linking text data.
Would anyone know what is causing this and how I could fix it?
Thanks,
Jake
  #2  
Old July 17th, 2008, 12:29 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Error message #N/A appears in linked cells

Could be the cells with #N/A are linking to cells with 255 characters?
Not much you can do about it if you want the full text to appear -
except to open the source workbook.

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

  #3  
Old July 18th, 2008, 07:23 AM posted to microsoft.public.excel.links
Jake
external usenet poster
 
Posts: 319
Default Error message #N/A appears in linked cells

Thanks for the suggestion Bill, but that's not the case. Some of the cells
contain much less than 255. Could there be something in the text that causes
Excel to display #N/A?

"Bill Manville" wrote:

Could be the cells with #N/A are linking to cells with 255 characters?
Not much you can do about it if you want the full text to appear -
except to open the source workbook.

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


  #4  
Old July 18th, 2008, 08:22 AM posted to microsoft.public.excel.links
Jake
external usenet poster
 
Posts: 319
Default Error message #N/A appears in linked cells

Here is a sample formula:
='\\sh02y291\Resource Management$\6300-50000\Period 3\[MPS Period 3
6300-50000 roll-ups.xls]MPS'!G5

There is only one source book, although I have other formulas with multiple
source books that don't have a problem.
No merged cells and the workbooks aren't shared.
Thanks
"Bill Manville" wrote:

I guess I must be missing something.

Please post an example of a formula that is delivering #N/A when the
source book is closed.

Is more than one source book referenced and if so are the errors
specific to one source book?

Are merged cells or shared workbooks involved at either end of the
link?

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


  #5  
Old July 18th, 2008, 08:55 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Error message #N/A appears in linked cells

I guess I must be missing something.

Please post an example of a formula that is delivering #N/A when the
source book is closed.

Is more than one source book referenced and if so are the errors
specific to one source book?

Are merged cells or shared workbooks involved at either end of the
link?

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

  #6  
Old July 18th, 2008, 03:17 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default Error message #N/A appears in linked cells

Jake wrote:
Here is a sample formula:


Nothing obvious, I fear - but you knew that.
Do the cells in the source book referenced by the formulas that return
#N/A themselves contain formulas?

If so, we might be looking in the wrong place and find that the cells
in the source book (as saved) themselves contain #N/A and so are being
faithfully represented. When you open the source workbook though it is
recalculated and you don't see the #N/A ??

I'm largely out of ideas - haven't experienced this issue myself - or
heard about it from anyone else. The only workaround I can suggest is
opening the source workbook.

I'm willing to look at the workbooks if you would like to send them
(though possibly the problem won't transfer with them). Bill
underscore Manville at Compuserve dot com

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

  #7  
Old July 19th, 2008, 09:54 PM posted to microsoft.public.excel.links
Barbara Wiseman[_2_]
external usenet poster
 
Posts: 20
Default Error message #N/A appears in linked cells

I get this error frequently, it is caused because the files being linked to
are large and the linked data can not be retrieved unless the source files
are open.



More annoyingly sometimes opening the files with the links in I get an error
message saying that there is not enough memory to open the file with the
links and then the file opens but with none of its formatting.

The only way round is to open the large source files first and then the file
which links to them.



I do rather bring it on myself by having files of several mb size linking to
about 6 - 8 other files, often 2 or 3 of these source files are large too.



My advise is to use manual calculation (calculate on save, which prevents
lost data when excel crashes, another side effect of numerous links) and to
always open any files being linked to (edit links, open source). Links to
unopened files can give error messages, or more worryingly inaccurate
results.

Barbara







"Bill Manville" wrote in message
...
Jake wrote:
Here is a sample formula:


Nothing obvious, I fear - but you knew that.
Do the cells in the source book referenced by the formulas that return
#N/A themselves contain formulas?

If so, we might be looking in the wrong place and find that the cells
in the source book (as saved) themselves contain #N/A and so are being
faithfully represented. When you open the source workbook though it is
recalculated and you don't see the #N/A ??

I'm largely out of ideas - haven't experienced this issue myself - or
heard about it from anyone else. The only workaround I can suggest is
opening the source workbook.

I'm willing to look at the workbooks if you would like to send them
(though possibly the problem won't transfer with them). Bill
underscore Manville at Compuserve dot com

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 07:58 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.