#VALUE ! errorr for links to other workbook

In one cell of my first workbook I have a link to the
cells of the other workbook. If the second workbook is
open, then the link formula is

=AVERAGE(OFFSET([Secondfile.xls]Sheet2!$A$2,MATCH(MAX
([Secondfile.xls]Sheet2!$A:$A),[Secondfile.xls]Sheet2!
$A:$A,0)-6,15,5,1))

and the calculated value is displayed in the first
workbook. Such value remain also after I close the second
workbook, only the formula changes in the part of the
address of the second file:

[Secondfile.xls]Sheet2! - 'C:\[Secondfile.xls]Sheet2'!

But if the second workbook is closed nad I close and
reopen the first workbook or if I chose " Edit -
Links... - Update Values" combination, then I get the
#VALUE! error in the cell of the first workbook.

I tried to change the posible parameters on "Edit Links"
form, I tried also all the combination of these options
with all of the combinations of "Tools - Option -
Calculation - Update remote references" options but with
no cuccess. Did I miss anything? Is there any connection
with my medium macro security?

Thanks in advance

Janez

Hi

Some functions don't accept links to closed workbooks as arguments - OFFSET
is one of them.

Mirror Sheet2 from Secondfile.xls into sepearte (hidden) sheet, using links
like
A1=IF('C:\My Documents\[Secondfile.xls]Sheet2'!A1="","",'C:\My
Documents\[Secondfile.xls]Sheet2'!A1)
and use this mirror sheet as source for AVERAGE.


Arvi Laanemets


"Janez Banez" wrote in message
...
In one cell of my first workbook I have a link to the
cells of the other workbook. If the second workbook is
open, then the link formula is

=AVERAGE(OFFSET([Secondfile.xls]Sheet2!$A$2,MATCH(MAX
([Secondfile.xls]Sheet2!$A:$A),[Secondfile.xls]Sheet2!
$A:$A,0)-6,15,5,1))

and the calculated value is displayed in the first
workbook. Such value remain also after I close the second
workbook, only the formula changes in the part of the
address of the second file:

[Secondfile.xls]Sheet2! - 'C:\[Secondfile.xls]Sheet2'!

But if the second workbook is closed nad I close and
reopen the first workbook or if I chose " Edit -
Links... - Update Values" combination, then I get the
#VALUE! error in the cell of the first workbook.

I tried to change the posible parameters on "Edit Links"
form, I tried also all the combination of these options
with all of the combinations of "Tools - Option -
Calculation - Update remote references" options but with
no cuccess. Did I miss anything? Is there any connection
with my medium macro security?

Thanks in advance

Janez