External Link - Conditional

[Guest]

I have a workbook that has external links. Except that it's a
sumif(external,internal,external) type formula. I have no problem clicking
'Update Links' in other workbooks and it will give me live information, yet
in this particular one, I have to manually go open the workbook for it to
update. Is there a limitation to the auto update due to it being in a formula
instead of just grabbing an exact value from an external? It's referencing 12
workbooks and it can be quite time consuming to open and close them all.
Thanks!

 

[Dave Peterson]

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))

Adjust the range to match--but you can't use the whole column.

If you create the formula with the book1.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

 

[Guest]

Yeah, I figured out shortly after my post that the sumproduct works. Thanks
for pointing out the others that might have problems. Also, thanks for all
the over suggestions you've had on previous quesions. You da man!!

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=SUMproduct(--('C:\yourfolder\[book1.xls]Sheet1'!$A1:$A99,A3),
('C:\yourfolder\[book1.xls]Sheet1'!$B1:$B99))

Adjust the range to match--but you can't use the whole column.

If you create the formula with the book1.xls workbook open, you may find it
easier. Excel will adjust the formula when you close that workbook.

I have a workbook that has external links. Except that it's a
sumif(external,internal,external) type formula. I have no problem clicking
'Update Links' in other workbooks and it will give me live information, yet
in this particular one, I have to manually go open the workbook for it to
update. Is there a limitation to the auto update due to it being in a formula
instead of just grabbing an exact value from an external? It's referencing 12
workbooks and it can be quite time consuming to open and close them all.
Thanks!