I opened two spreadsheets from an email, spreadsheet June & November. Both June & November in cell A1 reference a [CashInput.xlsx]CashInput$A$1 that was saved on the spreadsheet creator's hard drive. My machine does not have access to the creator's hard drive. The value in A1 is different between spreadsheets June (Value=1) & November (Value=2) when opened in read only mode because the value in CashInput$A$1 was changed before November was created.
However, when enabling editing on the spreadsheets, the first spreadsheet to be "enabled", whether June or November, will reflect the value in the other's A1 field. So June A1 will change to 2 if you first enable editing in June followed by enabling editing November. November A1 will change to 1 if you first enable editing in November followed by enabling editing in June. Nothing else changes (that you can see). If you enable editing without the other open the value stays the same.
Why does this happen? My theory is that 2 cells in the same instance of Excel with the same source cannot reflect different values, even if the source can't be reached. Is there anything that can prevent this from happening?