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 would like to prevent OpenOffice Calc or Microsoft Excel from updating absolute references when their target cells are moved.

For example, if cell B2 is "=$A$1" and you cut A1 and paste it in A2, B2 now says "=$A$2".

Is there any way to prevent this? It very easily leads to corruption of my carefully-crafted spreadsheets. (According to the OO.o issue tracker, it's a feature…)

share|improve this question

2 Answers 2

up vote 4 down vote accepted

You can try Excel's INDIRECT() function:

INDIRECT(ref_text,a1)

For example, if you always want to refer to cell A1, use the following syntax in cell B2:

=INDIRECT("A1")

In this fashion, B2 will always be equal to what A1 returns. For more info on this function, visit this page.

share|improve this answer
    
It's a bit of a hack, and requires some work for large spreadsheets, but it seems to work. Thanks a lot! –  Leif Arne Storset Feb 18 '10 at 23:31
    
You're welcome. –  Mehper C. Palavuzlar Feb 19 '10 at 7:53
    
Your answer inspired me to dig deeper in the function library. Turns out the OFFSET function will preserve AutoFill behavior but still prevent cut-paste corruption. So if I have values in A1:A10 I want to work with, I can use in B1 "OFFSET(B1;0;-1)" and autofill down. –  Leif Arne Storset Feb 21 '10 at 17:03

The only way I know is to copy and then delete the original call (and intuitively and from personal expectation as a user I'd say that adjusting references to a moved cell is the right thing to do more often than not).

share|improve this answer

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.