Cyberdude
10-14-2006, 05:11 PM
I have WorkbookA and WorkbookB.
In WorkbookA on Sheet2 I have defined a name SidsBal for a cell that will always contain the current balance for the data on Sheet2.
In WorkbookA on sheet Summary I have a cell that contains the formula =SidsBal which is supposed to fetch the value of SidsBal for display on sheet Summary.
In WorkbookA on sheet Summary I have another cell named SummTotal that sums up all the values similar to SidBal that are collected from other sheets in WorkbookA.
In WorkbookB I have a sheet that references (links to) the cell SummTotal in WorkbookA using a statement like:
'C:\EXCEL Documents\Stock Records.xls'!SummTotal
My problem is that there is a tendency for SummTotal (in WorkbookA) to not contain the latest value from SidsBal. This results in an incorrect value in WorkbookB, which is combined with the results of similar links to other workbooks. Sometimes I get the correct value ? many times I don?t. So I run a macro that opens and closes one-by-one all the workbooks that WorkbookB links to hoping that opening WorkbookA will give it a chance to update its internal links, which will update the WorkbookA summary sheet, so then WorkbookB will fetch an updated value for SummTotal.
However, that doesn?t seem to happen. Then when I open WorkbookA, the summary sheet looks OK, so I wondered what is the order of updating the links? It appears that the external link from WorkbookB to WorkbookA is being updated before the internal link within WorkbookA. This has been a nightmare to debug because by the time I open WorkbookA everything looks OK. Oh, I also tried using a .Calculate statement in the Workbook_Open procedure of WorkbookA, hoping that might force WorkbookA?s internal links to update, but it has no effect. I think I?ve found before that external link updates are done before anything else. Anyone else have this problem with links?
In WorkbookA on Sheet2 I have defined a name SidsBal for a cell that will always contain the current balance for the data on Sheet2.
In WorkbookA on sheet Summary I have a cell that contains the formula =SidsBal which is supposed to fetch the value of SidsBal for display on sheet Summary.
In WorkbookA on sheet Summary I have another cell named SummTotal that sums up all the values similar to SidBal that are collected from other sheets in WorkbookA.
In WorkbookB I have a sheet that references (links to) the cell SummTotal in WorkbookA using a statement like:
'C:\EXCEL Documents\Stock Records.xls'!SummTotal
My problem is that there is a tendency for SummTotal (in WorkbookA) to not contain the latest value from SidsBal. This results in an incorrect value in WorkbookB, which is combined with the results of similar links to other workbooks. Sometimes I get the correct value ? many times I don?t. So I run a macro that opens and closes one-by-one all the workbooks that WorkbookB links to hoping that opening WorkbookA will give it a chance to update its internal links, which will update the WorkbookA summary sheet, so then WorkbookB will fetch an updated value for SummTotal.
However, that doesn?t seem to happen. Then when I open WorkbookA, the summary sheet looks OK, so I wondered what is the order of updating the links? It appears that the external link from WorkbookB to WorkbookA is being updated before the internal link within WorkbookA. This has been a nightmare to debug because by the time I open WorkbookA everything looks OK. Oh, I also tried using a .Calculate statement in the Workbook_Open procedure of WorkbookA, hoping that might force WorkbookA?s internal links to update, but it has no effect. I think I?ve found before that external link updates are done before anything else. Anyone else have this problem with links?