Automatic Updating of OFFSET function retrieving data

[Tom Richardson]

Please can anyone help me with this problem?

I am using the following conditional formula to retrieve cell values from
another workbook:

=IF(OFFSET('[Comparative Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(13:13)-1)*7,0)=0,"",(OFFSET('[Comparative Weekly Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5,(ROW(13:13)-1)*7,0)))

It works well until I close the workbook, then the next time I open it
again, and click "Yes" on the "update automatic links to another workbook"
dialog box (which I must do to access the worksheet), all my cells change to
#VALUE!. After experimentation I discovered that the data was refreshed only
when I opened the original workbook my formula referred to (that is,
Comparative Weekly Takings Reports Year 4.xls).

Is there anyway I can automatically refresh my data when I open my workbook,
without also opening Comparative Weekly Takings Reports Year 4.xls, and not
creating the #VALUE! strings?

I have tried clicking ing on the "Refresh External Data" button, but nothing
happens - I'm still left with a column of #VALUE! strings..

 

[Frank Kabel]

Hi
no chance with this formula. OFFSET does not work with closed workbooks
(the same is true for SUMIF, COUNTIF and some other functions)