External links not updating how they should.

[xsmurf]

Hello,

I have a question regarding links to external workbooks.

I have a formula that checks other workbooks to retrieve values based on a date and shift, this code works perfect in another workbook however in my new workbook it doesn't respond properly.

The formula is being triggered by date and shift and these cells are updated when I open the workbook, but the values are not being retrieved.
When I manually change both date and shift the formula shows me the values.

I have checked and made sure the calculations are set to automatically, the links are proper even used macro's to make sure all values are recalculated, nothing seems to help.
There are links to 5 workbooks in my excel sheet, I don't think that's the issue.

Any help would be appreciated

The formula that I am using, with small change to the location:

=IFERROR(INDEX('\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$D$1:$D$1596,LARGE(IF(C3&F3='\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$B$1:$B$1596&'\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$C$1:$C$1596,ROW('\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$C$1:$C$1596),0),SUM(('\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$B$1:$B$1596=C3)*('\\serverlocation\Shared_Folders\Production Reports\[Production Report.xlsm]Production Reports'!$C$1:$C$1596=F3))-B5+1)),"")

 

[Anfinsen]

When you open the workbook, you get the error consistently, correct? Does the formula update if you simply press F9? (keyboard shortcut for calculate)

 

[xsmurf]

Thank you for replying @Anfinsen .

Yes I get the error consistently, no values being shown, just blank. The values stay blank even after pressing F9.
Only way to show the values is to manually change the date and shift.

 

[Anfinsen]

Is the cell where the formula exists formatted as "general"?

 

[Anfinsen]

One other thing to try, open the workbook, and confirm the formula shows the error.
Update all data in the workbook Press CTRL+ALT+F5, or on the Data tab, in the Connections group, click Refresh All.
I think it's possible the data connection is not being refreshed until you update the values you reference.

 

[xsmurf]

@Anfinsen I made sure the format of my cells are correct (thanks for the hint) and everything seems to be working now.
Thank you