MoveRange with formulas

Posted by: mikael-h on 8 September 2017, 3:04 pm EST

  • Posted 8 September 2017, 3:04 pm EST

    Hi

    I'm having a SUM function in a cell that sums the following 4 cells in the same row.

    I'm using the MoveRange function to move the 2 last cells in the SUM function further 'down' the same row.

       SheetView sheet = this.fpSpread1.Sheets[0];
       sheet.AutoCalculation = true;
       sheet.MoveRange(6,5,6,12,1,2,true);

    I would like the cell where SUM function is used to extend its range automatically to include the new location. The same way it is done in Excel.

    Is there a way to achieve this?

    Thanks,

    Mikael  

  • Replied 8 September 2017, 3:04 pm EST

    Mikael,

    Spreadsheet products (e.g. Spread, Excel, OpenOffice, Google Docs) have internal rules for when/how formulas are modified after user actions (e.g. move range, add rows/columns, remove rows/columns). Spread's rules are designed to closely match the other spreadsheet products (e.g. Excel, OpenOffice, Google Docs). However, the rulers for the other spreadsheets do not always match. For example, consider the case of the move range action. OpenOffice's rules only modify the formula's referenced range when the move's source range completely contains the formula's reference range. Google Docs's and Excel's rules also modify the referenced range (by expanding to include the move's destination range) when the move's source range contains the last column of the formula's reference range and the move is horizontal to the right. Excel's rules also modifies the formula's reference by shrinking the formula's referenced range (to exclude the move's source range) when the move's source range contains the last column of the formula's referenced range and the move is horizonal to the left (as long the move is not beyond the first column of the formula's referenced range). As you can see, there is no universal agreement on exactly what the rules should be for the move range action as the other three products (i.e. Excel, OpenOffice, Google Docs) each have slightly different rules. In the case of the move range action, Spread's rules follow OpenOffice's rules.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels