External Links and OFFSET

  • Thread starter Thread starter BUDDY
  • Start date Start date
B

BUDDY

I have a workbook that links to another, but requires the
second one to open in order to update. Here is the
kicker: it is true for only some cells. The only
commonality of the troublesome cells is that I use the
OFFSET function. HELP?!

TIA
 
Here is an example.

=SUM(OFFSET('[SHA ACCOUNTS.xls]DATA'!$CJ$677,0,0,1,-C4))
Click to expand...
...

Since you want cells in row 677 from somewhere to the left of col CJ to col CJ,
this could be rewritten as the *array* formula

=SUM((COLUMN(INDIRECT("A:CJ"))>=COLUMN(INDIRECT("CJ"))-C4)
*IF(ISNUMBER('[SHA ACCOUNTS.xls]DATA'!$A$677:$CJ$677),
'[SHA ACCOUNTS.xls]DATA'!$A$677:$CJ$677))
 
Back
Top