External Links and OFFSET

[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

 

[Peo Sjoblom]

Yes, offset does not work on closed workbooks.
There might be a workaround if you post the formula..

Here is a brilliant workaround
by Harlan Grove

http://tinyurl.com/yvwb7

 

[BUDDY]

Here is an example.

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


Thanks!

 

[Harlan Grove]

Here is an example.

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

...

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