Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul134
 
Posts: n/a
Default Why sometimes excel returns error just because function parameters are linked?


Why sometimes excel returns N/A or REF, just because function parameters
read from a linked sheet that is not open? Is it a bug? How can I avoid
it?

I understand the paths change accordingly whether the linked file is
open or not not, but that shouldnt be a problem:

For instance if A1 reads ='[workbook1.xls]sheet1'!A1 when the link is
open, it automatically changes to:
'C:\myfolder\workbook1.xls]sheet1'!A1, and that never represents a
problem, it will show the right value even if the current book is
calculated, or the cell copied/moved to another location, or pressing
F2 and enter.

Even if I use, for example VLOOKUP, MATCH or MID, with parameters
linked to a file that is right now closed, they work well. OFFSET and
CELL, on the other hand, return VALUE and N/A error, when the link
closes

I will not check all the excel functions, but perhaps if I could
understand why this happen with the ones I need right now I could
prevent the error on the future

The temporary solution is to open all linked files to make the errors
disapear. But that is not practical if linked files are a lot and are
really big...

I've tried unsuscesfully several excel versions, so I presume is not an
excel bug relative to a specific version...

Thanks. Paul


--
Paul134
------------------------------------------------------------------------
Paul134's Profile: http://www.excelforum.com/member.php...o&userid=31735
View this thread: http://www.excelforum.com/showthread...hreadid=516265

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Wright
 
Posts: n/a
Default Why sometimes excel returns error just because function parameters are linked?

Some functions will simply not allow you to reference a closed workbook.
It's not a bug, it's simply the way they were designed. Various ways to
deal with it, including opening up the source workbooks, or pulling in all
the data using direct links into a helper sheet, and then doing your calcs
against the helper sheet.

Try googling the newsgroups on 'closed workbook' and you'll get various
ideas on solutions.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Paul134" wrote in
message ...

Why sometimes excel returns N/A or REF, just because function parameters
read from a linked sheet that is not open? Is it a bug? How can I avoid
it?

I understand the paths change accordingly whether the linked file is
open or not not, but that shouldnt be a problem:

For instance if A1 reads ='[workbook1.xls]sheet1'!A1 when the link is
open, it automatically changes to:
'C:\myfolder\workbook1.xls]sheet1'!A1, and that never represents a
problem, it will show the right value even if the current book is
calculated, or the cell copied/moved to another location, or pressing
F2 and enter.

Even if I use, for example VLOOKUP, MATCH or MID, with parameters
linked to a file that is right now closed, they work well. OFFSET and
CELL, on the other hand, return VALUE and N/A error, when the link
closes

I will not check all the excel functions, but perhaps if I could
understand why this happen with the ones I need right now I could
prevent the error on the future

The temporary solution is to open all linked files to make the errors
disapear. But that is not practical if linked files are a lot and are
really big...

I've tried unsuscesfully several excel versions, so I presume is not an
excel bug relative to a specific version...

Thanks. Paul


--
Paul134
------------------------------------------------------------------------
Paul134's Profile:
http://www.excelforum.com/member.php...o&userid=31735
View this thread: http://www.excelforum.com/showthread...hreadid=516265



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Paul134
 
Posts: n/a
Default Why sometimes excel returns error just because function parameters are linked?


Thank you Ken.

Paul.


--
Paul134
------------------------------------------------------------------------
Paul134's Profile: http://www.excelforum.com/member.php...o&userid=31735
View this thread: http://www.excelforum.com/showthread...hreadid=516265

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
@ and "function not valid" error (Excel 2003) Acert93 New Users to Excel 1 September 2nd 05 07:17 AM
Problem with linked Excel table in a word Document jcarlos Excel Discussion (Misc queries) 1 August 5th 05 07:41 PM
CONVERT Function Disappered in Excel Gord Dibben Excel Discussion (Misc queries) 3 April 13th 05 07:59 PM
Excel function help facilities RPS Excel Discussion (Misc queries) 1 December 8th 04 03:36 AM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM


All times are GMT +1. The time now is 01:14 PM.

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

About Us

"It's about Microsoft Excel"