I don't understand this behaviour

[Luis_C]

I have this doubt that I would like to clear out. Probably a conceptual thing that I am not aware of.

Lets say that I have a number 6 (or whatever) in B3. A number 5 in B6. In C3 this formula: =B3+2. It appears 8. Now delete the entry in B3 and drag the content of B6 to B3 (or just drag and replace the content of B3). I would expect a 7 in C3 (5+2), but I get #REF . This happens only when dragging the content of a cell to other cell that is referred in a formula somewhere. A #REF will appear and not the evaluation of the formula.

Why happens this?

Thank you.

Luis

 

[davidam]

Doesn't happen for me. I get 7

 

[Eric W]

Put 6 in B3, 5 in B6, and =B3+2 in C3. You'll get a value of 8 in C3. Now delete column B. C3 becomes B3, and the value turns to #REF . This is because the formula in C3 refers to a cell that no longer exists.

Now go back to the original set-up. And drag cell B3 to B2. Look at the formula in C3 - it now says =B2+2. When you drag the cell like that, Excel thinks that you still want to refer to the cell you're dragging, you just want to put it somewhere else on the sheet for appearance.

Back to the original set-up. Add a formula in C6: =B6+5. Now drag B6 to B3. The formula in C6 now refers to B3, and the C3 formula shows #REF . The B3 cell has essentially been destroyed, which renders all formulas pointing to it invalid. That's why Excel asks if you want to continue.

Once more back to the original set-up. Go to the B6 cell, COPY the value, and paste it into B3. The formula in C3 is fine. So you have to mentally differentiate between the cell as an object, and the value within the cell.

Hope this helps.

 

[Luis_C]

Thank you very much, Eric. So it is a conceptual thing after all... Sometimes tiny things like this ruins a whole day of hard work...

Luis
(Chrome refused to post my reply quoting your response )

 

[Peter_SSs]

(Chrome refused to post my reply quoting your response )

Just so you know, it wasn't Chrome refusing to show your post. It was the forum anti-spam software that occasionally picks up posts as possible spam that it shouldn't.

 

[FDibbins]

...Now delete the entry in B3 and drag the content of B6 to B3 (or just drag and replace the content of B3). I would expect a 7 in C3 (5+2), ...
Luis

When you drag a formula to another cell, the formula does not change, it does not adjust.
=B3+2 dragged to B6 will still be =B3+2, not =B6+2
It will only adjust if you copy/paste