Keeping Calc column constant

[trenoweth]

I’m sure this is simple but I just can’t get my head round it!

I have a spreadsheet where the cells in column D show the sum of all the values in columns E to V (presently)

I.e. D3=sum(E3:V3)

If I add a new column before the present column E then the formula in D3 changes to =sum(F3:W3) and this remains the same no matter how many $ signs I add in various places.

What I want to do is have the formula in D3 change to =E3:W3) when a new column is added immediately after column D

I’m sure it’s something I’ve set up years ago in a different spreadsheet so feel sure I should be able to do it in LO Calc.

[anon73440385]

What I want to do is have the formula in D3 change to =E3:W3) when a new column is added immediately after column D

You can’t achieve a range changing this way, if you insert a column outside of the range.

[Zizi64]

Use an empty helper culumn in column E (and summarize it too). Then insert a new column INSIDE the summarized cell range: after column E or before column F.

[trenoweth]

Many thanks for your reply

[anon73440385]

Hello,

try =SUM(OFFSET($D3;0;1;1;COLUMN(V3)-COLUMN($D3))).

This formula changes to:

=SUM(OFFSET($D3;0;1;1;COLUMN(W3)-COLUMN($D3)))

if you insert a column after $D3, since you insert within a range (i.e. within $D3:V3).

Note: COLUMN(V3)-COLUMN($D3) is equal to COLUMNS($D3:V3)-1

Hope that helps.

[trenoweth]

Many thanks for your reply

[sokol92]

The reference behavior specified in the question is controlled by the Calc parameter “Expand references when new columns / rows are inserted”

[trenoweth]

Many thanks for your reply

[eeigor]

Expand references when… as you were told.

Or try to rebuild the table so that the column with the sum would not be on the left, but on the right, making the leftmost column constant.

For example, D will be the first column (RC4), and U will be the last. Enter the formula in column V (RC[-1] here refers to U):

V3: =SUM(INDIRECT(“RC4:RC[-1]”;0))

The formula will be the same for all rows.

Now you can insert new columns anywhere. Freeze the leftmost column (D) for convenience.

No “expand references” are required.

The INDIRECT function is also volatile as the OFFSET one.

There is another way. Check out my answer here.

D3: =SUM(INDIRECT(“RC[1]:RC100”;0))

RC100 refers to the right cell so that it is enough (if it is not enough, make more).

[trenoweth]

That’s brilliant, many thanks indeed. By changing RC4 to RC5 I can then show the total from the right hand column in column D which, as there are columns for each week of the year,is the perfect solution.

[trenoweth]

I was sure that I had done this in a spreadsheet some 25 years or so ago, when I was using an Amiga with a program the name of which escapes me, but it was done a different way from today’s solution. Many thanks to all those who posted a response.