Reference not updating

Question

I'm working with two different books in Excel. Book2 has a reference to Book1. 

Value in Book2 updates correctly when I change value in Book1. Problem is if I move cell in Book1 (insert row).

The reference to the cell is then lost. What is wrong?

Reference looks like this =[leverandorbok.xlsx]Fisk!$D$2

Booth books are stored locally on computer but in a google drive shared folder.

Answer 1

Hi Arne,

The two workbooks need to open at the same time to update the references. If you want to change the source, the best practice it to open the other workbook at the same time. This way, all the changes will update and you can save the source workbook to a different location and still have them linked.

Regards,

Alex

Answer 2

Hi Alex,

Excel version 1901, build 11231.20174

Windows 10 version 1803, build 1734.590W

This is my computer, but workbooks are sometimes used on other computers as well.

Issue seems to occur if I make changes to the source book while workbook with reference in it is not open. Cell value updates when I open workbook again, but if I have moved the source cell workbook with reference does not update.

Thanks

Arne

Answer 3

Hi Arne,

I just test from my side. When both of the workbooks open, insert rows or columns in the data source workbook, the reference changes in the other workbook.

To narrow down this issue, you can test the performance in a new blank workbook. link a cell and edit the data source workbook see if the same issue happens.

For further support, can you share the Office version and Windows version with us:

Office version: any Office app > File > Account > About

Windows version and build: press Windows key + R > type winver > OK

Regards,

Alex