I've got a Excel file with three sheets. The first two are for entering data, the last one shows a summary and some calculations.
In the first one, there are several columns for entering an address. In the summary sheet, those are combined into a single cell:
=Sheet1!C1 & Chr(32) & Sheet1!D1 & Chr(32) & Sheet1!E1 & ...
The file was used by plenty of my users, and now one the address summary cells only shows the #REF! error. In the formula, only one of the concatenated fields is #REF!, the others are valid.
=Sheet1!C1 & Chr(32) & Sheet1!#REF! & Chr(32) & Sheet1!E1 & ...
Usually this happens if a referenced cell's column or row is deleted, but this is not possible as the sheets are password-locked for modification (except entering data, of course). The summary sheet with the formulas is read-only. And as mentioned, only a single field in a single address row is affected. The referenced cell has text in it.
I now wonder what else could cause this #REF! error. I have unfortunately no possibility to track the changes the users made. What other ways are there to break the reference to a cell despite the sheet being locked?