Inserting columns breaks formulas on other sheets

Aspose.Cells Product Family
1

Let’s assume I have a spreadsheet with 2 worksheets, Sheet1 and Sheet2. On Sheet2 I have a formula =Sheet1!B1

When I insert a column on Sheet1:
Workbook.Worksheets[0].Cells.InsertColumn(0);

the formulas on Sheet1 are correctly updated, but formulas on other sheets are not, so the above formula will stay =Sheet1!B1 instead of becoming =Sheet1!C1 (Excel handles this correctly and updates references on all sheets)

Is there a workaround to fix formulas that refer to other sheets when inserting a row/column on the referred sheet?

Thanks!

2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the latest version Aspose.Cells
for .NET v7.5.0.1 and see if it resolves your issue.

If your problem still persist, please provide us your runnable sample code replicating this issue. We will look into it and update you asap.

3

The problem indeed persists with the latest version of Aspose.Cells for .NET. I just downloaded the latest version and built a simple project.

Use the attached Excel file. It basically has a value on Sheet1 in cell A1 (5000) and a formula on Sheet2 in cell A1 (=Sheet1!A1). The code is below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose.Cells;

namespace Bug1
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook(“Test.xlsx”);
wb.Worksheets[0].Cells.InsertColumn(0);
wb.Save(“Test1.xlsx”);
}
}
}

What I expect to happen is Test1.xlsx, I expect to see an extra column on Sheet1, and this happens, my value has now moved to cell B1, but I also expect my formula on Sheet2 to now point to B1 (as Excel would do if I insert a column on Sheet1). The formula on Sheet2 does not get updated and it stays =Sheet1!A1 and the result now becomes 0 (as I inserted an empty column).

Thanks!

4

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. The formula in another sheet breaks. We have logged this issue in our database. We will look into it and fix this issue. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41772.

5

Hi,


Please change your sample code with v7.5.0.1 as follows (see the line in bold):

Workbook wb = new Workbook(“Test.xlsx”);
wb.Worksheets[0].Cells.InsertColumn(0, true); //true refers to updateReference boolean parameter which means formulas in other sheets would be updated accordingly.
wb.Save(“Test1.xlsx”);

I have tested the updated code with your template file and it works fine.

Thank you.


6

This fixed the problem, thank you!

7

Hi,


Good to know that your issue is fixed using the other overload of InsertColumn method.

Feel free to contact us any time if you have further query or have some other issue, we will be happy to assist you soon.

Thank you.