3D cell reference returns #REF! when deleting worksheets

Aspose.Cells Product Family
1

Hi,

I've hit upon a problem with 3D cell references: if a worksheet within the 3D range is deleted the reference returns a #REF! error. This differs from the behaviour of Excel which allows worksheets to be added or removed if they are between the first and last worksheets of the reference. To see how Excel behaves:

  1. Open a new workbook with 4 worksheets.
  2. In cell A1 on Sheet1, enter the formula =SUM(Sheet2:Sheet4!A1)
  3. Delete Sheet3 - the formula will not be affected.

Unfortunately Aspose.Cells behaves differently. Opening the same 4-sheet workbook as above, with the same formula in Sheet1!A1, if Sheet3 is deleted using wb.Worksheets.RemoveAt("Sheet3"), the formula on Sheet1 becomes =SUM(#REF!) which is incorrect. This should only happen if the first or last worksheet of the range is deleted.

I would appreciate if you could look into this.

Many thanks, Glynn

2

Hi,


Thanks for providing some details.

After an initial test, I observed the issue as you mentioned. I used my template Excel file (attached), I created it following the steps mentioned by you. I opened the Excel file via Aspose.Cells APIs, removed the third sheet and re-saved the file. When I opened the output file into MS Excel, it shows “#REF!” error on the formula cell in the first sheet. Ms Excel behaves differently and computes the formula fine even removing the sheet in the workbook. I am using the following sample code:
e.g
Sample code:

Workbook workbook = new Workbook(@“e:\test2\Bk_3dcell1.Xlsx”);
workbook.Worksheets.RemoveAt(“Sheet3”);

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

I have logged a ticket with an id “CELLSNET-42954” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
3

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET v8.2.0.3 and let us know your feedback.