Change cell relations in another file even if this file is closed
I have 2 Excel files, let’s call them A.xlsx and B.xlsx.
Some of the cells are connected: in file A there might be cell C3 for example like “=[B.xlsm]example!D4” and otherwise round.
If I close file A and add a row in file B that changes the cell names of all following cells, the former cell D4 is now e.g. E4.
Now I open again file A, and the reference in cell C3 still reads “=[B.xlsm]example!D4” … but it should be E4 because the correlated data is now in cell E4!
Result: Cell C3 in file A shows the wrong data (from file B, cell D4 instead of E4)!
And only because file A was not opened during the changes in file B.
When file A is open while file B is changed like described above, then the changes are transferred to file A correctly.
The same problem occurs with rows: if target cell D4 changes to D5 in one of the files while the other is closed, the closed file doesn’t get that change and when I open it, the source cell shows the wrong data.
It’s a totally reproducible issue, no matter how simple or complex the 2 Excel files are or if they contain formats or macros or not.
The Office support told me, this is “normal behaviour” of Excel, but I can’t take that: relative referrers in Excel are always changing if I change the columns or rows, that’s the key feature of Excel as I understand it! In addition, it can’t be wanted that the results of changes in one file depend on if the other file is opened or closed! And last of all: when cell contents (values) are updated over files even if the second file is not open during the chages, why shouldn't cell relations? That doesn't seem logical for me ...
You may decide if this is an improvement suggestion or a bug report ... ;))
Kenneth Barber commented
You could try combining your files. That would solve the issue.
If we keep the files separate, then Windows 10 would have to have a radically different treatment of files. Each file would have to be part of a tree of precedents and dependents. When one file in the tree changes, the others are updated.