Excel automatically changing relative reference to text

New Contributor

I have several Excel files that have relative references within one sheet to data entry fields in other sheets within the same Excel file.  Lately, the relative references within cells get converted to the text data that they reference from the other spreadsheets. So the resulting spreadsheet no longer references the data entry fields in the other sheet.


At first, I thought it was happening when I closed the spreadsheet (or of the file was incorrectly closed), but now I've noticed it happening while I'm still working within the spreadsheet.  I'm unaware of any settings that have changed, but I can't figure out why I'm losing the relative references.

3 Replies
did you try to use the INDIRECT function to use text as references

@Ramiz_Assaf  Thanks, but no, I'm trying to find out why it's all of the sudden happening and preventing it.  There is no reason for Excel to convert the referenced data to permanent text (and remove the embedded cell references) unless some setting has changed I'm unaware of.  


It can help us all if you upload an Excel file (without sensitive data), no picture. So you could get a precise solution much faster. This would also be a blessing for us, as we can understand the problem much better, a win-win situation for everyone.


I can only guess that there is no Excel file (and I'm not good at that). Suspect because the translation of your request sometimes also plays a role.



I know I don't know anything (Socrates)