Array Formula not updating

[collis47]

I have a quite a large array formula:

{=(INDEX('[Plant Movements Entry.xls]Data'!$E$2:$E$9997,MAX(('[Plant
Movements Entry.xls]Data'!$A$2:$A$9997=A3)*ROW('[Plant Movements
Entry.xls]Data'!$A$2:$A$9997))-CELL("row",'[Plant Movements
Entry.xls]Data'!$A$2)+1))}

for some reason it wont update properly unless the other workbook which
it refers to is open, if the other book is not open it returns error
values.

its strange because i have used the same formula in other books with
out incident

can any one shed some light on why this may be?

Cheers

 

[T. Valko]

The problem is the CELL function.

You don't need it: (array entered)

=INDEX('[Plant Movements Entry.xls]Data'!$E$2:$E$9997,MAX(('[Plant
Movements Entry.xls]Data'!$A$2:$A$9997=A3)*ROW('[Plant Movements
Entry.xls]Data'!$A$2:$A$9997))-1)

Since your data starts in row 2 you only need to offset by -1 row for the
Index to work properly.

Biff