How to avoid formula to change when I add a new row?

[mslankey]

Hey everyone,

I have tabular data starting from A7 to currently G33. I regularly have to add a new row and to avoid having to scroll I add it at the top, making it a “new A7” row. Above the table data I have a cell with the formula that should span all the table and I want to avoid to touch often:

=SUMIFS($F$7:F42;$B$7:B42;"";$G$7:G42;"<>CC";$G$7:G42;"<>Call")*100

But as soon as I add a new row it changes to:

=SUMIFS($F$8:F43;$B$8:B43;"";$G$8:G43;"<>CC";$G$8:G43;"<>Call")*100

slipping down one row and not including the newest row. How can I avoid this auto-change?

Thank you in advance

[bcronald]

Hi!
Please go to menu Tools >> Options
Under the ‘LibreOffice Calc’ tree node >> General
Under the heading ‘Input Settings’
Tick the check box ‘Expand references when new columns/rows are inserted’
Press Apply and OK
Now it should expand your selection as per your requirement.

[mslankey]

Great, that works. Thank you!