Excel 2010 - Must open all workbooks for links to update 1

[Eprice]

Hi,
We are converting our XP templates to 2010. I have a workbook that has links to seven other workbooks. In the old version, when we opened the main workbook (Time.xls) it would ask us if we wanted to update links. If clicked Update then all the links would update (as they should). In the 2010 version, if we click update - the links do not update. Click on Edit Links and Status is Unknown. Click on Update Values get Error: Source not found. Then if click on Open Source - the other workbook opens and the workbook links are updated. It should not be that way, we don't want to have to open all the workbooks. I have checked and all workbooks are set to automatically update links. I have even wiped out the links and started over since they are now .xlsm and then Changed Source in the Edit Links dialog box but nothing works. For some reason the other workbooks have to be opened before the links will update. I have run out of ideas of what to do or fix. Is this a known problem with 2010 or am I missing something. Please help.
Lisa

 

[Eprice]

I also started with a blank workbook, saved it as Test.xlsx and in one cell linked to one cell on another workbook and have the same problem, it will not update unless both workbooks are open.

 

[GlennUK]

Are all the relevant folders included in your Trusted Locations?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Eprice]

Yes, I made the folder a trusted location. I did find out the reason is because they are all .xlsm. I took out the macros and saved it as xlsx and it works fine. There has to be another work around for this problem.
Lisa

 

[zelgar]

The problem is probably because of the Macro protections are still set to ON. Probably the best option is to have the spreadsheet that is linked to be set up that to have 2 versions: one being the macro enabled sheet and another version that is is a .xlsx that is updated whenever the macro enable sheet is updated.

 

[Eprice]

Ok, seems like a lot of trouble to have to set up two different workbooks just to have external links. Thanks anyway.
Lisa