No change of address to a cell in calc, when new lines are inserted.

Albireo · February 1, 2019, 10:17am

Should try to explain my wish with an example (not easy) assume the following .:

Cell B3 contains the string "Info1"
Cell B4 contains the string "Info2"
Cell B5 Is an empty cell

Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B4" - shows "Info2"
Cell D5 contains the formula "=B5" - shows "0"

Don’t know why “0” appears in cell D5, but I usually handle it with the formula OM(B5 = ""; ""; B5) (Swedish LO) I can handle the above. But…, assume that an empty cell is inserted between B3 and B4, the following results occur.

Cell B3 contains the string "Info1"
Cell B4 Is now an empty cell
Cell B5 contains the string "Info2"
Cell B6 Is an empty cell

Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B5" - shows "Info2"
Cell D5 contains the formula "=B6" - shows "0"

My wish is that the formulas in cells D4 and D5 should not change. I want still show the contents of cells B4 and B5 like this.

Cell D3 contains the formula "=B3" - shows "Info1"
Cell D4 contains the formula "=B4" - shows "0"
Cell D5 contains the formula "=B5" - shows "Info2"

Is it possible?

How?

mikekaganski · February 1, 2019, 10:30am

You’d need formulas like =OFFSET(D3;0;-2) in D3.

Albireo · February 5, 2019, 5:16pm

I have tried to translate =OFFSET(D3;0;-2) ( OFFSET(Reference; Rows; Columns; Height; Width) ) to swedish LO and it must be =INDEX(D3;0;-2) ( INDEX(Referens; Rad; Kolumn; Område) )
Wrote that formula in cell D3. But I got the result "Err:502". Right now I don’t know why.

simeva · February 1, 2019, 1:34pm

Or you could use a different method to insert the space below B3.
Highlight the range B4 - D5.
(either just those cells, or rows B-D, or all the rows below row B - depending on what you want to happen to any other data you have there).
Drag the selection down one row.
This will keep your original references intact.

simeva · February 1, 2019, 1:54pm

btw, you can stop zero values appearing as “0” by changing the number format. You can make them appear as a blank space, a dash ( - ), whatever you want.

Albireo · February 5, 2019, 4:20pm

Thanks for the tip! Now I understand…

This setting (“0”) is set by default. And should I make it work on all computers that Libre Office is installed on, do I have to make the change to the default template on all computers? (if I have understand correctly)

Albireo · February 10, 2019, 9:22am

I was wrong! (The OFFSET... is not INDEX... in Swedish LO.)

=OFFSET(D3;0;-2) is the same as =FÖRSKJUTA(D3;0;-2) in Swedish LO. (then it works - thank you Mike)

Another way to solve my wish is to use this formula (in Swedish LO) .: =INDIREKT("Sheet1." & "B" & RAD(D4)) Can maybe be translated to =INDEX("Sheet1." & "B" & ROW(D4)) in cell D3 (English LO).

These two ways have different limitations. For example “INDIREKT” (Swedish LO). Can be copied to other cells and retain the contents of the cell it points to.

mikekaganski · February 11, 2019, 2:09pm

I prefer to use English function names - checking a Use English function names checkbox under Options→LibreOffice Calc→Formula.