Array formula not updating

Question

I have the following array formula on a workbook (Main workbook)

{=INDEX('C:\Users\Antonio\Documents\client PA[TDde.xls]Account'!$E$1:$E$65536,MATCH(1,('C:\Users\Antonio\Documents\client PA[TDde.xls]Account'!$A$1:$A$65536="NetLiq")*('C:\Users\Antonio\Documents\client PA[TDde.xls]Account'!$H$1:$H$65536="BASE"),0))}

Which finds an entry in a table that satisfies two criteria.

My problem is that when workbook TDde.xls is open the formula above does not often (sometimes it does) change to

{=INDEX('[TDde.xls]Account'!$E$1:$E$65536,MATCH(1,('[TDde.xls]Account'!$A$1:$A$65536="NetLiq")*('[TDde.xls]Account'!$H$1:$H$65536="BASE"),0))}

Since values in TDde.xls change based on DDE link updates, the first formula does not update. The second, does.

All non-array formula that refer to TDde.xls update well when the TDde.xls workbook is open.

If I re-enter the array formula while TDde.xls is open, all works well: If I close the main workbook and open it again, it updates live.

However, if I close TDde.xls while the main workbook is open, and then re-open the main workbook, then, from then on, the links refer to the saved TDde.xls and do not update live.

Where is the issue?

Thanks,

Antonio

Answer 1

Hello Harrow,

Yes, I have tried that already and it works.

The problem is that when I close close TDde.xls the links in the main refer to the saved TDde.xls and they stay like that even if I reopen TDde.xls.

Thanks.

Answer 2

Delete the linking for the formula and re-create it, check if the data updates instantly after creating the new link (Without restarting the workbook).

Harrow