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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

Thank you Ken.

Paul.