Cells.InsertRows() and Cells.DeleteRows() don't update formula and chart references


#1

Please see the attached template file. I’ve discovered the following behavior with the above methods:

1) Cells.InsertRows() dynamically updates formula references but not chart ranges.
2) Cells.DeleteRows() does not dynamically update formula references. It may also affect chart ranges, though there’s no way to test that until InsertRows is fixed.

To confirm that Excel should behave this way, insert a row between rows 3 and 4 in the template and add some data. You’ll see the sum and chart update itself. Do the same using InsertRows() and the formula will update but the chart will not.

Now reload the template, insert a row between 3 and 4, then delete row 5. Both the chart and formula update themselves. Do the same with the DeleteRows() method and you should get a circular reference error because the formula did not update itself.

Thanks, Natan.


#2

Quick follow-up:

I tested the InsertRow() method and it dynamically updates the chart. I would rather use InsertRows() though.


#3

Also, please make sure that pivot tables are dynamically updated as well.

Thanks.


#4

Please try this attached fix.


#5

This hotfix did not resolve the problem. Charts are not dynamically updated using the InsertRows() method. I also get a circular reference error because DeleteRows() is not updating formulas.


#6

Please download and try v3.4.5 at www.aspose.com/downloads . Bug in DeleteRows method are also solved.


#7

The InsertRows() method seems to be fixed. I’m still having trouble with DeleteRows() method. Charts and formulas are not dynamically updated when rows are deleted from the spreadsheet. To see Excel do this, do the following:

1) Put values in A1 and A2.
2) A3 gets the formula SUM(A1:A2).
3) Insert a row between rows 1 and 2 and put a value in A2.

The sum is now SUM(A1:A3).

4) Delete row 3 and the sum is now SUM(A1:A2).

With 3.4.5 I’m still getting a circular reference error because the cell references are not updated. Charts are also affected in a similar way.


#8

Please try this fix.


#9

Third time’s a charm. Thanks!