How do I move a formula without changing it?

users
#1

I have some rate-of-change triangles I need to change. These show the annual rate of change for different numbers of years and look like this:

2010 X X X X X
2011 X X X X
2012 X X X
2013 X X
2014 X

Basically, I want to reverse the lines, moving the top to bottom and vice versa. I want to do this so the multiple years go out from the ending date instead of the starting date. Like this:

2010 X
2011 X X
2012 X X X
2013 X X X X
2014 X X X X X

If I just copy and paste and the cells aren't protected by '$' the cells change accordingly. I don't want that to happen. I want the formulae to reference the same cells. I just want to move them to another location. Is there any way to do this?

#2

Yes - add $ to the cell references in the formula - e.g., A2 becomes
$A$2. That means you can move the formula anywhere you want but it will
still reference A2...

#3

I know that but that is what I am trying to avoid. I would have to change it to copy and then change it back after I move it so I can drag the next year.

I am looking for a way to copy and paste the existing formulae without changing them. This may not be possible but I thought I would check. If I can't do that I will have to investigate to see what the fewest number of cells I actually must change are and take it from there.

#4

In a spreadsheet I needed to set the background color in the three cells A1, B1, and C1, depending on the value of B1.
Example:
If the cell B1 contains "A" all the 3 cells will be colored. At all other values in B1 nothing happens.
In a couple of hours I found a solution, but not in the Help section of LibreOffice. Perhaps I just didn't recognize it? Some hidden help :-).
I think there should be something in Help about how to format cells ranges depending on the content of one cell. I guess I am not the only one who needs this function sometimes.

Curious about the Solution? Here it is:
Select the actual cells range. Open the Conditional Formatting Dialog and select condition type "Formula is". As a formula enter (in this example) $B1="A". (The $ sign *must* be added). Choose or create a style that suits you and press the OK button (or press Enter).

Kolbjoern

#5

You might have the answer in your own question, even I am not quite ready in my thinking about this.

If you move the formula (e.g. via the sequence ctrl+X and ctrl+V at the new position, well you can do it via the edit menu instead) the references do not change, the formula still points at the same cells as in the first position. It is when you copy the formula the references change, as they are relative. However to really get them absolute, you have to anchor them with the "$" sign.

#6

That is very cool. I tried it and it works. I didn't realize there was a difference between copying and cutting.