Offset function in Excel

  • Thread starter Thread starter Grant Asher
  • Start date Start date
G

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
 
It is because offset only works on open workbooks..
Click to expand...

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))
 
Back
Top