Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .