Cannot use offset formula to access another workbook

Hi all,

In workbook1, sheet1, A1 I have the formula

=workbook2, sheet1, A1

(yes I know i need brackets and things!)

and I get the correct value.

However, if I change the formula to

=offset(workbook2,$A$4,1,1)

(again I have dispensed with formatting so that the principle is clear)

I get #value when the second workbook is closed, but correct value when second workbook is open.

Numerous tests have allowed me to pick up the value in a cell in a different workbook by direct reference, but it always fails if offset is used.

The workround is to install the offset requirements in the second worksheet and then pick up the values by a direct cell reference, but does anyone have any ideas as to why this is occurring?

thanks,

Bob

For the benefit of all our readers, the formula below retrieves values from a closed workbook. Note also that I have applied sumproduct to rows rather than columns. This has much simplified what I need to do each month.

"period" is the accounting period i am interested in, "offsettest1" is the closed workbook.

Carim, your near instant response guided me to this solution. Many thanks. Next time you come to England, I will buy you a Guinness / Real ale / Cranberry Juice.

=SUMPRODUCT(('U:\moved_data\RCF\[OFFSETTEST1.xls]Sheet1'!$A$1:$D$1=period)*('U:\moved_data\RCF\[OFFSETTEST1.xls]Sheet1'!$A$2:$D$2))