Externa links - the original formula is being replaced with the external link

[Sadhna]

Hi,

I have two workbooks. In Book 1, Cell C11, the formula is =C124.
In Cell C 124 of Book 1, the formula is = 'https:///personal/Test_com/Documents/Documents/[Source.xlsx]Sheet1'!$B$5

I then save and close both files.

When I open book 1, Cell C11 formula is replaced with 'https:///personal/Test_com/Documents/Documents/[Source.xlsx]Sheet1'!$B$5 instead of =C124

This is not happening with every workbook. Why is this happening and how do I fix it?

 

[Monty]

Hi

It seems like there might be a circular reference issue in your Excel setup. Circular references occur when a formula refers to its own cell either directly or indirectly. In your case, Cell C124 references Cell C11, and Cell C11 references Cell C124, creating a circular reference loop.

To fix this issue, you can try the following steps:

1. Enable Iterative Calculation.
Go to Excel options, select the Formulas tab, and check the "Enable iterative calculation" box. This allows Excel to handle circular references by iterating through the calculations until a stable solution is reached.

2. Break the Circular Reference:
Edit the formulas to remove the circular reference. You may need to redesign your workbook structure to avoid such circular dependencies.

3. Use Absolute References:
Ensure that your references are absolute when necessary to prevent inadvertent changes when moving or copying formulas.

By breaking the circular reference or adjusting your workbook settings, you should be able to resolve the issue of formulas being replaced when reopening the workbook.