I have a formula in, let’s say A1, =sum(a5:a500). I have a range of data starting at A5, e.g. 1, 1, 1, 1, 1. So =sum(a5:a500) = 5. I want to insert a row at row 5 and add a new number but when I do, that changes the A1 formula to =sum(a6:a501). I’ve tried $a$5:$a$500, but that doesn’t seem to solve the problem.
Probably just missing something.
The ‘$’ (to declare an “absolute reference”) becomes effective when you copy a formula, not when the formula is moved due to adding (or deleting) a row and/or column.
Let’s take your example with 450 formulae / column pointed at a single cell. You now need to add another row, and all 450 formulae become invalid, because the cell that they point at has moved but their references remained static. What if you have 50,000 of them? Do you fancy changing every one by hand?
Copying formulae from one cell to other cells as you build a spreadsheet is common, and the absolute referencing is designed to facilitate that. On the other hand, almost no-one wants the referencing to remain static when rows/columns are added/removed.
Here is some extra when the menu option:
Menu/Tools/Options/LIbreOffice calc/General - Expand references... is selected (or not):-
=SUM($A$5:$A$9) is placed in cell A1
1 is placed in cells A5,A6,A7,A8,A9
Default (Expand references... is NOT selected):-
Add a row at A5:
(or) Add a row at A10:
Non-Default (Expand references... IS selected):-
Add a row at A5:
(or) Add a row at A10:
If this helps then please tick the answer (
).
It works as must be expected.
I think the option:
Menu/Tools/Options/LIbreOffice calc/General - Expand references when new columns/rows are inserted.
must solve your. It just modify the behaviour.
Hi m.a.riosv
Always good to come across something new.
That affects rows/columns added next to existing references (If you insert rows or columns in the middle of a reference area, the reference is always expanded). It also does not stop rows being pushed down (if added above) or up (if deleted above).
My answer expanded in the light of this.
How about A1=SUM(INDEX(A1:A500,5):INDEX(A1:A500,500)) ?
As INDEX doesn’t return a reference but a value I cannot understand how the suggested formula should work with the intended result. In fact it should produce an error even in the slightly “internationalized” form (Locales where the comma is used as the decimal delimiter cannot also accept it as a parameter delimiter.):
(A1:) =SUM(INDEX(A1:A500;5):INDEX(A1:A500;500)) .
If working at all it would also adapt in the unwanted way.
@ lolax Do you mind giving additional explanation?
Hi @Lupp maybe the explanation is in LIbreOffice help: “INDEX returns a sub range, specified by row and column number, or an optional range index. Depending on context, INDEX returns a reference or content.”
It works for me in LibreOffice Calc or MS Excel.
@mariosv Sorry!
As help texts often are not optimally clear I didn’t consult this one. Instead I referred to the mandatory specification (OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format) which doesn’t mention the “context sensitivity” causing references to be returned under conditions by INDEX. INDIRECT and OFFSET, however, are specified to return references. A test I also performed must have been spoilt by a typo or whatever.
@lolax Sorry again. I should have tested more thoroughly. (As I always use OFFSET and/or INDIRECT for similar purposes I was bad in understanding your suggestion.) Sorry again! I can no more edit my wrong comment. (The last few words “…it would also adapt in the unwanted way.” should, however, be correct.)
There is no matter @Lupp, usually I forgot to use it in that way, even more I have read recently that it seems better for performance than e…g OFFSET() because it is not volatile and is recalculated only when precedents changes while OFFSET() is always recalculated.
It’s okay and thanks for providing those methods.
We were thoroughly informed of the settings and the working of automatic range adaption on inserting (also deleting) rows (or/and columns).
If the results of such an adaption are not acceptable under the circumstances we have to express our expectations more clearly. This can be done in more than one ways. With regard to the original example some of these are:
(A1):
=SUM(INDIRECT("$A$5"):INDIRECT("$A$500"))
=SUM(INDIRECT("$A$5:A$500"))
=SUM(OFFSET($A$1;5-1;0;500-5+1;1)
The third of these suggestion is by far best adaptable to changing needs. It will, however change automatically into
=SUM(OFFSET($A$2;5-1;0;500-5+1;1))
on inserting a row above the (old) first row. To avoid this, too, you may use:
=SUM(OFFSET(INDIRECT("$A$1");5-1;0;500-5+1;1))
=EDITING=
Please note that parts of my comments on the solution suggested by @lolax were wrong. Sorry1
Hi
Without changing or options or formulas: cut (Ctrl+X) line 1 (or A1), insert line 5, paste line 1 (Ctrl+V)
Regards
Ctrl-X Ctrl-V doesn’t solve this; formulas in unrelated columns are still screwed with, just as if you’d inserted cells. Try it (nine-row simple example: ColumnA = 1 to 9; ColumnC = 1 to 9; ColumnB = =IF(B1=D1,“yes”,“no”) cut+paste columnC causes ColB formulas to change )
Some bonehead must’ve gone out of their way to write software to recognize what you’re pasting is similar to what was cut, and screw with formulas. Maddening garbage. v5.3.3.2
Anyone have a solution?
Here’s a crap workaround: Cut, Paste, Undo, Paste again
Another is to: Cut, do something else, Paste;
where “do something else” means type some garbage or something into an unrelated cell (evidently you have to make bonehead’s software “forget” that you are pasting what was just cut).