[Solved] Adjust range in formula when inserting column

[quarkrad]

Struggling with this one. In the attached E4 totals the numbers in B4, C4 and D4. I would like to insert an extra column after D and call E3 year 4 with E4 having a value of 10. I would like the Total column, which has now moved to column F, specifically cell F4 to show the new total 29. But it doesn't because no matter how many new columns you insert before the Total column the formula remains as =SUM(B4:D4) . Any advice appreciated

[karolus]

Hallo

SUM($B4:INDEX(4:4;COLUMN()-1))

[Alex1]

When I insert a column E, your formula is adjusted automatically.
Make sure the following setting is checked: Tools > Options, LibreOffice Calc > General > Expand references when new columns/new rows are inserted.
I would expect this setting to be stored in the file, but I'm not sure.

[quarkrad]

Thank you both very much. Appreciate your time helping me.

[cwolan]

Make sure the following setting is checked: Tools > Options, LibreOffice Calc > General > Expand references when new columns/new rows are inserted.
I would expect this setting to be stored in the file, but I'm not sure.

There is a configuration parameter called ExpandReference (set to false by default). The appropriate entry may appear in the registrymodifications.xcu file.
⠀

inCONFIG.png (117.84 KiB) Viewed 199 times

[MrProgrammer]

But it doesn't because no matter how many new columns you insert before the Total column the formula remains as =SUM(B4:D4)

This is explained in [Tutorial] Formula Adjustments during Copy and Move in the section about inserting cells.

If my option OpenOffice Calc → General → Input settings → Expand references when new columns/rows are inserted is off, when I select column E in your attachment and use Insert → Columns, F4 remains =SUM(B4:D4). However, if the option is on, when I select column E and use Insert → Columns, F4 changes to =SUM(B4:E4). Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms.

Perhaps you want a formula expression which means: the columns of this row from B to the column on the formula's left. Selecting cell E4, I use Insert → Names → Define, Name → Years → Assigned to → $B4:D4 → Add → OK. I typed only one $, as shown. Relative references in a defined name are relative the cell which was active when I used Insert → Names → Define, E4 in this situation. Relative to E4, $B4 means: this row column B. Relative to E4, D4 means: this row one column to the left. In E4, I change the formula from =SUM(B4:D4) to =SUM(Years). Now when I select column E and use Insert → Columns, F4 will still contain =SUM(Years), and it will sum the values in B4:E4, whether the option is on or off, since E4 is the cell to the left of F4. If you don't understand relative references read section 8. Using formulas and cell references in Ten concepts that every Calc user should know.

Read about defined names in Help → Index or in User Guides (PDF) or searching for topics about this in the Calc Forum.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

The appropriate entry may appear in the registrymodifications.xcu file.

This is in in my registrymodifications.xcu

   <item oor:path="/org.openoffice.Office.Calc/Input">
      <prop oor:name="ExpandReference" oor:op="fuse">
         <value>true</value>
      </prop>
   </item>

I do not find a corresponding entry in any of the XML files created by performing unzip on an ODS file, so I believe for OpenOffice this is per-user setting and not a per-file setting.