We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Referencing a Cell on another Worksheet

Suppose you have a workbook with two worksheets, sheet1 and sheet2. From sheet1 you reference a cell from sheet2. For example

=sheet2!A10

Now with Aspose.Cells you open that workbook and insert three rows into sheet2 before row 10. It seems, as if the reference still points to sheet2!A10. However, it should now point to sheet2!A13.

Am I doing something wrong? Should this work?

Michael G. Schneider

Hi Michael,

Thanks for considering Aspose.

Well, you should use InserRows(rowindex, rownumer, updateref) overloaded version of the method. I tried your scenario and it works fine as it points to "Sheet2!A13". Following is my testing code, I used a simple template file.

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
workbook.Open(@"d:\test\testsheet.xls");
Worksheet sheet2 = workbook.Worksheets[1];
Cells cells = sheet2.Cells;
cells.InsertRows(9,3,true);
workbook.Save ("d:\\test\\testsheet_out.xls");

I tried the latest version 4.4.3 for your info.

Thank you.

And here are my template input and output files.

Thank you.

Hello Amjad,

thanks a a lot for the answer. You are right, if using InsertRows with the third parameter True, the references are updated.

I think the situation regarding updating cells in other worksheets is not perfect. Let me make some suggestions.

  • I used InsertRow not InsertRows. It is strange that InsertRow does not offer a third parameter. I used Intellisense, so I did not learn about the fact that the user has to take care of updating the references himself.
  • I think that updating all references should be done automatically. If I modify an existing workbook, I want its integrity to be valid all the time. So setting a third parameter to True should not be needed.
  • You probably introduced the third parameter for performance reasons. Why not create a new workbook method “AlwaysKeepTheReferencesCorrect”? If that is set to True, Aspose.Cells will automatically update the references in other worksheets of the same workbook. It is easy for you, and the user does not have to think of manual update when inserting/deleting columns/rows/ranges.

Michael G. Schneider

Hi Michael,

Thanks for your suggestions.

We will check / evaluate your suggestions and get back to you soon.

Thank you.

Hi Michael,

We will add a new overloaded method for InsertRow method to allow you to update cross-sheet references.

Yes, we don't do the task automatically for performance reason. We will check if we can improve the peformance to update cross-sheet references first. If we cannot find a way, we will provide a option flag like "AlwaysKeepTheReferencesCorrect" as you suggested.

Hello Laurence,

thanks a lot for the answer.

I do not quite understand your “performance statement”. You try to optimize the cross-sheet performance first. Then, if you find a way for better performance, what would you do? Always update cross-sheet references, no matter what the user gave as the third parameter?

Michael G. Schneider

If we can optimize the cross-sheet performance or we think the consumed time is acceptable, we will update cross-sheet references automatically if you don't specify third parameter.

But we will still keep the third parameters for users to get better performance.

Is it clear?

Hello Laurence,

thanks a lot for the answer.

Yes I do understand. Technically it means changing the default value for the last parameter from False to True. This means changing the “contract between Aspose.Cells and its users”. However, I think the possibility that it breaks some currently correct code, is very small.

Michael G. Schneider