[Solved] Keeping Cell References from changing in a formula

[Solved] Keeping Cell References from changing in a formula

I'll use a simple example for my problem:
In cell C3 I have the formula =\$A\$3-\$B\$3. The problem is I need to be able to cut cell B3 and post it elsewhere, or delete cell B3 and shift the cells below it up. If I cut cell B3 and paste it in, say, D3, the formula in cell C3 changes to =\$A\$3-\$D\$3. I could copy cell B3 and paste it in D3 and then delete cell B3 and shift the cells below it up but then I get a reference error in cell C3. How do I or is there even a way to make the formula in cell C3 remain the same no matter what I do to the cells it refers to?
Last edited by Joe-H on Wed Nov 18, 2009 7:23 pm, edited 1 time in total.
Open Office 3.0.1
Ubuntu 9.04
Joe-H

Posts: 2
Joined: Wed Nov 18, 2009 6:07 pm

Re: Keeping Cell References from changing in a formula

Calc tries very hard to keep the formulas referring to the same value, so you get the same result even if you move an input cell to a different location. I don't know of any way to turn that off.

You can avoid it by copying the cell to a new location, then overwriting the input cell with a different value.

If you have to move the input cell, and you want to avoid Calc adjusting a reference to the moved cell, you have to use a different way to refer to the cell, e.g. instead of C3:=\$A\$3-\$B\$3, you could use C3:=\$A\$3-OFFSET(C3;0;-1).
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Keeping Cell References from changing in a formula

Thank you!The Offset function appears to work as I need.
Open Office 3.0.1
Ubuntu 9.04
Joe-H

Posts: 2
Joined: Wed Nov 18, 2009 6:07 pm