[Solved] Keeping Cell References from changing in a formula

Discuss the spreadsheet application

[Solved] Keeping Cell References from changing in a formula

Postby Joe-H » Wed Nov 18, 2009 6:28 pm

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

Postby acknak » Wed Nov 18, 2009 6:38 pm

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

Postby Joe-H » Wed Nov 18, 2009 7:18 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests