Offset function in Excel

[Grant Asher]

I'm having problems with links updating in Excel when I
use the "Offset" worksheet function. I have several cells
with the formula =sum(offset(link1!B1,0,0,1,match(A1,Link1!
A1:N1,0))). When I first open the sheet I get a "Value"
error message. The source files are absolutely fine, and
I can get ordinary linked cells to update without any
problems. When I open the file that it links to the
values then update without any problems.

Can anyone help?

Thanks in advance for your assistance.
Grant Asher

 

[Peo Sjoblom]

It is because offset only works on open workbooks..

 

[Harlan Grove]

It is because offset only works on open workbooks..

Begging the question how to handle this when linked workbooks could be closed.
...

So summing a range beginning at Link1!B1 spanning 1 row and a number of columns
given by the MATCH call, so a dynamic range.

One approach would be to use the array formula

=SUM((COLUMN(INDIRECT("B:IV"))<=MATCH(A1,Link1!A1:N1,0))
*IF(ISNUMBER(Link1!B1:IV1),Link1!B1:IV1))

 

[Peo Sjoblom]

Brilliant..