Workbook links

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Hi there,


I'm using excel 2016and have three macro-enabled worksheets that have links to each other, The files are not that big, 7mb the biggest and have been created and sent from another source, though in the latest excel version.

The worksheets can be unstable for some reason particularly on our network and they lose the link to each other and you get the #REF! statement in the linked cells. I've got into Edit links option when prompted and can see if the worksheets are available.

I don't know if it is the way I'm opening them or I need to do something elsewhere under options, I don't really know and would appreciate any ideas, corrective measures?

Regards

Ben
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,849
Are the workbooks in the correct Directory according to the coding within ?

You say you received them from another source ... what Directory were each of the workbooks in when they were at the source location ?
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Are the workbooks in the correct Directory according to the coding within ?

You say you received them from another source ... what Directory were each of the workbooks in when they were at the source location ?[/QUOTE]
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
The sheets have no coding, just formulas, i.e. MATCH, etc. I've extracted them from the zip file they were sent and you can see the relevant links when you go to the Edit Links for files option.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,849

ADVERTISEMENT

Whenever you receive the
Code:
[COLOR=#333333] #REF! statement[/COLOR]
, it is referring to a link within the cell, most likely to another
cell involved in the formula.

From the M$ website: https://support.office.com/en-us/ar...EF-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be

[FONT=&quot]The [/FONT][FONT=wf_segoe-ui_semibold]#REF![/FONT][FONT=&quot] error shows when a formula refers to a cell that’s not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over.[/FONT]

Somehow, during the transition of 'zipping' the file at the source location and you opening it on your computer, something was changed. Either a formula, a cell
or perhaps a column or cell got deleted. Strange occurrence to be certain.

You'll need to review the entire project to determine what was changed. Don't know of any other way.
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Whenever you receive the
Code:
[COLOR=#333333] #REF! statement[/COLOR]
, it is referring to a link within the cell, most likely to another
cell involved in the formula.

From the M$ website: https://support.office.com/en-us/ar...EF-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be



Somehow, during the transition of 'zipping' the file at the source location and you opening it on your computer, something was changed. Either a formula, a cell
or perhaps a column or cell got deleted. Strange occurrence to be certain.

You'll need to review the entire project to determine what was changed. Don't know of any other way.[/QUOTE]
 

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
234
Office Version
  1. 365
Platform
  1. Windows
Ok. I've been using the Edit links in excel 2016 to ensure the cells can read each other and hardcoded some numbers that didn't need formulas. Some people do go over board and use formulas for the sake of it when the data is never going to change!

Cheers

Ben
 

Watch MrExcel Video

Forum statistics

Threads
1,122,497
Messages
5,596,508
Members
414,073
Latest member
Contilly

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