Updating formula with reference to other worksheet

Aspose.Cells Product Family
1

I have a worksheet “Blad1” with some data and two formula’s in G9 and G11 (see the attachment test.xlsx). Whenever I insert new rows after row 3, I expect the formula’s to update and in Blad1 they do. A$1:B$5 in G9 gets updated to A$1:B$9 and =A5 in G11 gets updated to =A9.

I have the same formula’s in worksheet “Blad2” but here they reference the data in Blad1, these formulas are not updated like they are in Blad1 and so the results are not correct. See attachment saved.xlsx for the results.

How can I fix this? I’ve tried several things, like recalculating the worksheet, but that doesn’t help. If I rename “Blad1” the reference to Blad1 does get updated, but not the cell reference.

2

Hi,


Thanks for providing us template file.

Well, you should pick the suitable overloaded version of the method i.e., Cells.InsertRows (int rowIndex, int totalRows, bool updateReference) in your code and specify the last Boolean parameter to true, it will update the formula in the second worksheet accordingly, see the sample code below for your reference. I used your template file and used the following sample code, it works fine.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\test.xlsx”);
workbook.Worksheets[0].Cells.InsertRows(3, 4, true);


workbook.Save(“e:\test2\out1.xlsx”);

Let us know if you still have any issue.

Thank you.
3

I missed that option completely, so simple. It works perfect now, thanks for the support :).

4

Hi,

Thanks for your feedback and using Aspose.Cells.

It is good to know that the updateReference parameter in InsertRows() method worked for you. Let us know if you encounter any other issue, we will be glad to look into it and help you further.