#Value and #Ref, links between multiple file not updating

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124
I have a series of excel files linked to other files. Sometimes the workbook opens with #value, or #REF but the links are not broken... The value ONLY update and show up as numbers (vs errors) when I have the correspnding source file open.

I tried to update the links via "edit links" and Update values. Sometimes when I do that, cells with actual numbers turn into the errors #REF or #value. I then have to open the workbook to get rid of them.

Please help!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Certain functions (such as INDIRECT) require the referenced file to be open. What's the formula that is giving you #REF!'s?
 

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124
All the formulas are direct links "= file path and cell name
There are some vlookups but there are no fancy formulas....
 

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124

ADVERTISEMENT

No. i have not receied an answer to this
 

beerw0lf

Board Regular
Joined
Mar 7, 2006
Messages
103
I'm having the same issue. I am very interested in a solution as I deal with several files with several worksheets in each. I have links to 3 different files. The links to 2 of the files seem to update fine but I need to open the third to have the cells and formulars update.


Thanks
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

Please post the formula that's giving you a #REF! error when the referenced file is closed.
 

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124
=IF(EF$5="Actual",SUMIF('[2007 BU Actuals.xls]0107USD'!$A:$A,$A11,'[2007 BU Actuals.xls]0107USD'!$G:$G)/1000,ET11)
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
There is nothing inherent in that formula that would cause you to get a #REF! error.

Have you checked your referenced cells to see if any of them have #REF! errors? If there was even one #REF error in '[2007 BU Actuals.xls]0107USD'!$A:$A or '[2007 BU Actuals.xls]0107USD'!$G:$G, you'd get a #REF! error returned.
 

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
124
This particular formula i listed previously returns #value and refreshes to a number when I open the source file. The source files are all hard coded.
 

Forum statistics

Threads
1,136,652
Messages
5,677,008
Members
419,668
Latest member
DharmaK

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
Top