up vote 1 down vote favorite
share [g+] share [fb]

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

link|improve this question
feedback

2 Answers

up vote 1 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.

link|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
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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