OFFSET: Returns error when used to a linked file

[Arturo]

Hi,

I'm trying to do a dynamic sum function for a daily production given table.

File A is a summary, with many tabs for each product type. File B contains
the daily production detail.

File A has a sumif function:
* Condition range (first parameter) is located in file B, having the product
type list.
* Condition (second paramente) is provided in file A (product type).
* Sum range (third parameter) is located in file B, is the result of a match
function that looks for a given date, then returns the column number to look
for, and then brings a reference to the whole column (using OFFSET).

The problem I'm experiencing is that the cells in File A shows #REF if the
file B is closed. Is there any parameter in Excel to prevent this? (I dig in
every single option an none of them works). Or, is there any alternative
function to OFFSET to bring a whole column as array having a dynamic
parameter?

Thanks in advance.

 

[Harlan Grove]

The problem I'm experiencing is that the cells in File A shows
#REF if the file B is closed. Is there any parameter in Excel to
prevent this? (I dig in every single option an none of them
works). Or, is there any alternative function to OFFSET to bring a
whole column as array having a dynamic parameter?

....

There is no option or parameter than avoids the #REF! error. OFFSET
can return only references to ranges, and ranges usables as such exist
only in open files.

There are likely to be workarounds, but you need to provide your
current formula for others to respond with what would work best. But I
can say that references into closed workbooks are returned as arrays,
and through Excel 2003 arrays can't span entire columns. Do you really
need all rows from 1 to 65536? If you have column/field headings in
row 1, you'd need at most rows 2 to 65536.