Link between two workbooks don't update

[Arian355]

Hi

I have to workbook A that has some data (table) and workbook B that has a sumif function and is linked (defined name) with the data in the source workbook A.

When both the workbook are opened the link works, when the source workbook is closed and I open the destination workbook the link doesn't update or gives a

#VALUE! error.



I have tried,

1. Convert both workbook in .xlsb

2. On the data tab, Connection, edit links, startup prompt, "don't display the alert and update links"

On the data tab, Connection, edit links If I click update values, the status is OK but the link doesn't update

3. Excel Options, Trust Center, Trust Center Settings, Trusted Locations i have add the location of the folder where both excel workbooks are

4. Excel Options, Advanced, When calculating this workbook, Update link to other documents

 

[blazy]

Hey,

It is my understanding that you will not really be able to do this. When you have a formula with a reference to another workbook or worksheet, and you delete the workbook or worksheet, you will return #REF errors not #NA errors - as well the formulas wont update if both files are not open (correct me if I'm wrong people...)

Personally, I think that having links to workbooks within formulas is sloppy and causes all sorts of unwanted problems further down the line.

Why not have this calculations in the same workbook? If you really do need to have it in different workbooks, than creating macro will be a better idea.

KR