Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Invalid cell reference errors occur when a spreadsheet formula contains incorrect cell references. This happens most often when:

  1. Columns or rows containing data used in the formula are accidentally deleted.

  2. A formula has been moved or copied to another cell and the cell references are incorrect.

  3. Data used in a formula has been moved, leaving the function or formula with incorrect cell references

You need to go through it and find the error I'm afraid manually.

Source

share|improve this answer
    
I've been on that page, it didn't help me, then I carefully read the third suggestion from your answer again. "Data used in a formula has been moved". That was the problem. When you cut the value of a cell and paste it elsewhere, all formulas referencing the original cell are changed to reference the new cell. I didn't know about this behaviour of Excel. –  JMayer Dec 17 '12 at 13:16
    
You can use the term called Indirect in your formula. This means that it fixes on the cell regardless of what happens (or use absolute $ cell references) –  Dave Dec 17 '12 at 13:18
    
I am able to reproduce this behavious even with absolute references, so I'll have a look at Indirect. –  JMayer Dec 17 '12 at 13:22

I assume people could still do a cut and paste if they entered data to the wrong column? When there is a cut and paste take place, the formula would be messed up as well.

share|improve this answer
    
They can copy-paste in the data sheets. But there are no formulas in them, so only the entered values are copied, which I just tested again. No REF. They can't modify the formulas in the summary sheet at all. –  JMayer Dec 17 '12 at 13:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.