Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Link - Conditional
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Link - Conditional
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. Corey wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
External Link - Conditional
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!! "Dave Peterson" wrote: 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. Corey wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FInding external link | Excel Discussion (Misc queries) | |||
How do I use a cell value as the filename in an external link? | Excel Discussion (Misc queries) | |||
How do I use a cell value as the filename in an external link? | Excel Worksheet Functions | |||
External link using calculated cell value | Excel Worksheet Functions | |||
Save External Link Values | Excel Discussion (Misc queries) |