Ask Your Question
0

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

asked 2019-02-01 11:17:08 +0200

Albireo gravatar image

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?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2019-02-01 11:30:41 +0200

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

edit flag offensive delete link more

Comments

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.

Albireo gravatar imageAlbireo ( 2019-02-05 18:16:50 +0200 )edit
0

answered 2019-02-10 10:22:52 +0200

Albireo gravatar image

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.

edit flag offensive delete link more

Comments

I prefer to use English function names - checking a Use English function names checkbox under OptionsLibreOffice CalcFormula.

Mike Kaganski gravatar imageMike Kaganski ( 2019-02-11 15:09:17 +0200 )edit
0

answered 2019-02-01 14:34:28 +0200

simeva gravatar image

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.

edit flag offensive delete link more

Comments

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.

simeva gravatar imagesimeva ( 2019-02-01 14:54:58 +0200 )edit

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 gravatar imageAlbireo ( 2019-02-05 17:20:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-01 11:17:08 +0200

Seen: 40 times

Last updated: Feb 10