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

CA_User

Board Regular
Joined
Nov 5, 2004
Messages
126
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Certain functions (such as INDIRECT) require the referenced file to be open. What's the formula that is giving you #REF!'s?
 
Upvote 0
All the formulas are direct links "= file path and cell name
There are some vlookups but there are no fancy formulas....
 
Upvote 0
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
 
Upvote 0
Please post the formula that's giving you a #REF! error when the referenced file is closed.
 
Upvote 0
=IF(EF$5="Actual",SUMIF('[2007 BU Actuals.xls]0107USD'!$A:$A,$A11,'[2007 BU Actuals.xls]0107USD'!$G:$G)/1000,ET11)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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