[Solved] Make formulas adjust after inserting rows

[COKEDUDE]

I have several formulas like this:

=SUM(C15:C20)

After I insert rows I want it to change to this:

=SUM(C15:C26)

Is there an easy way to make it auto adjust to this? I always have to go in there and manually change it. Is there an easier or automatic way to do this?

[robleyd]

Go to Tools | Options | OpenOffice Calc | General and under Input settings ensure Expand references when new columns/rows are inserted is checked.

From the offline help - F1:

Expand references when new columns/rows are inserted

Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.

Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, the reference is always expanded.

[COKEDUDE]

Go to Tools | Options | OpenOffice Calc | General and under Input settings ensure Expand references when new columns/rows are inserted is checked.

From the offline help - F1:

Expand references when new columns/rows are inserted

Specifies whether to expand references when inserting columns or rows adjacent to the reference range. This is only possible if the reference range, where the column or row is inserted, originally spanned at least two cells in the desired direction.

Example: If the range A1:B1 is referenced in a formula and you insert a new column after column B, the reference is expanded to A1:C1. If the range A1:B1 is referenced and a new row is inserted under row 1, the reference is not expanded, since there is only a single cell in the vertical direction.
If you insert rows or columns in the middle of a reference area, the reference is always expanded.

OMG THANK YOU. That has been driving me crazy for awhile. Any idea why that is not a default feature?

Will the Expand Formatting option keep the currency option with the "$" dollar sign enabled? I already selected an entire column and enabled the currency feature but I keep having to enable it again.

https://pixhost.org/show/408/65907268_l ... atting.jpg

[robleyd]

Did you try it? Help says:

Expand formatting
Specifies whether to automatically apply the formatting attributes of the selected cell to the empty adjacent cells. If, for example, the content of the selected cell has the bold attribute, this bold attribute will also apply to adjacent cells. Cells that already have a special format will not be modified by this function. You can see the range in question by pressing the Ctrl + * (multiplication sign on the number pad) shortcut. This format also applies to all new values inserted within this range. The normal default settings apply to cells outside this range.

If it doesn't do what you want, consider the script in [Calc, Python]Expand/shrink list ranges.