#REF issue when closing down file - links are missing when reopening files

casco

New Member
Joined
Sep 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi

I'm having a problems with a number of linked files.

The setup I have is that I a master file to which I have links from a number of other files. Every time I open my master file the links to all the other files has been broken this is even after I have refreshed the master file and allowed editing to happen.

I then relink the master file to the necessary files and links will work while the all the files are open I will save them all. Once I then reopen the master fil all the links are back at #REF status.

The files in question have not been moved from folder to folder, all the files are in .xlsx format,, the files haven't been renamed, the refresh data button does not solve the problem - so it is not issues like this that is causing the problem

Any suggested solution are welcome.

I use Microsoft 360 on a computer that are a few years old

Many thanks for your help
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,026
I had a similar issue, then realized I should close the source workbook last. Especially if the formulas were a bit complex.
Reference formulas seem to be ok with closed workbooks, but complex formulas need to have the source workbook opened, closing the source workbook, and if the destination workbook recalculates, boom, #ref
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,686
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your question seems to have contradictory information in it. Does the master workbook have formulas linking to other workbooks, or do the other workbooks have formulas linking to the master?

Also, what exactly are you refreshing in the master?
 

casco

New Member
Joined
Sep 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I had a similar issue, then realized I should close the source workbook last. Especially if the formulas were a bit complex.
Reference formulas seem to be ok with closed workbooks, but complex formulas need to have the source workbook opened, closing the source workbook, and if the destination workbook recalculates, boom, #ref

many thanks Daveexcel I will give this a try and see if this solves the issue.

Thanks
 

casco

New Member
Joined
Sep 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Your question seems to have contradictory information in it. Does the master workbook have formulas linking to other workbooks, or do the other workbooks have formulas linking to the master?

Also, what exactly are you refreshing in the master?

Hi RoryA

The master sheet is linking to other source sheets not the other way around.

Thanks
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,686
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What exactly are the links? Are they simple links to cells, or formulas that do calculations (eg SUMIF or COUNTIF, which will not work if the source workbook is closed)?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,567
Messages
5,548,820
Members
410,874
Latest member
sriramvangara
Top