External links not updating how they should.

xsmurf

Board Regular
Joined
Feb 24, 2007
Messages
55
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
=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)),"")
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
When you open the workbook, you get the error consistently, correct? Does the formula update if you simply press F9? (keyboard shortcut for calculate)
 
Upvote 0
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.
 
Upvote 0
Is the cell where the formula exists formatted as "general"?
 
Upvote 0
Solution
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.
 
Upvote 0
@Anfinsen I made sure the format of my cells are correct (thanks for the hint) and everything seems to be working now.
Thank you
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top