#VALUE ! errorr for links to other workbook

[Janez Banez]

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

 

[Arvi Laanemets]

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

 

[Harlan Grove]

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 ....
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.

....

The cause of this problem is that OFFSET's first argument must be a range
reference, and as far as Excel is concerned ranges only exist in open files.
When you close the referenced file, Excel doesn't recalculate the linked
values due to minimal recalculation - closing the file means the values
won't change. However, if you force recalculation, OFFSET will return
errors, and they're propagate through other functions calling OFFSET.

Your offset call with 1st arg referring to row 2 but 2nd arg the result of a
search beginning in row 1 makes it appear you want the 5 rows in col P
starting with the 4th row above and ending with the row in which the topmost
MAX in col A is found.

You can do this in a single formula as long as you're not actually using
ranges that span all 65,536 rows. If you're only using rows 2..1001, try the
array formula

=AVERAGE(IF(ABS(ROW(INDIRECT("1:1000"))-2
-MATCH(MAX([Secondfile.xls]Sheet2!$A$6:$A$1000),
[Secondfile.xls]Sheet2!$A$6:$A$1000,0))<=2,
[Secondfile.xls]Sheet2!$P$2:$P$1001))

The trick here is to make the conditional 1st argument to IF TRUE for the 4
rows immediately above as well as the row including the topmost MAX value in
col A. I'm using A6:A1000 because any MAX in A1:A5 would result in fewer
than 5 cells from P2 down to and including the row containing the MAX. If
the first MAX in A6:A1000 were in row 34, then I'd want to average P3034.
The MATCH call would return 29. That gives the sequence 1..1000-2-29
= -30..967, so the 29th entry in -30..968 would be -2 and the 33rd entry 2,
which corresponds in P21001 to P3034. The reason for the ABS construct
is to avoid calling the MATCH term twice.