Copying formulas with cell referecing other sheets not working as expected

Aspose.Cells Product Family
1

Hi,

I have a workbook with following formula in cell G3 in Sheet1 which is referencing Sheet2. When I try to copy this formula to cell C3 in Sheet1, the relative cell reference part is the same as source and won’t ajust to destination cell. Please reference attachment for reproduction. The formula in cell C3 in Sheet1 is not correct.

=SUMIFS(Sheet2!G$3:G$7,Sheet2!$B$3:$B$7,Sheet1!$B3)

I am expecting the result formula to be like following as the same behavior as Excel. How to achieve this behavior when copying formulas?

=SUMIFS(Sheet2!C$3:C$7,Sheet2!$B$3:$B$7,Sheet1!$B3)

AsposeTest.zip (19.1 KB)

2

@wwu

To achieve the expected behavior when copying formulas in Aspose.Cells, you need to ensure that the formula references adjust correctly based on the destination cell. By default, when copying a formula, the relative references may not update as they do in Excel.

You can use the Copy method of the Range class to copy the formula while ensuring that the references adjust correctly. Here’s a code snippet to demonstrate how to do this:

// Load your workbook
Workbook workbook = new Workbook("path/to/your/workbook.xlsx");

// Access the first worksheet
Worksheet sheet1 = workbook.Worksheets[0];

// Define the source and destination ranges
Range sourceRange = sheet1.Cells["G3"];
Range destinationRange = sheet1.Cells["C3"];

// Copy the formula from the source to the destination
destinationRange.Formula = sourceRange.Formula;

// Save the workbook
workbook.Save("path/to/your/updated_workbook.xlsx");

This code will copy the formula from cell G3 to C3, and the references should adjust as expected. Make sure to replace the file paths with your actual file locations.

If you encounter any issues, please let me know!

3

I am afraid for this use case your code is not working. I tried the code and the result is the same.

4

@wwu
By testing with sample files and code on the latest version v25.9, we can reproduce the issue. Copying formulas with cell referecing other sheets is not working as excel.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-59103

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

Thank you for the swift response. Hope this issue will be fixed as soon as possible because there are no other ways to achieve the same.

6

@wwu
Thank you for your feedback. You are welcome. We will notify you promptly once there are any updates.

7

@wwu,

We are pleased to inform you that your issue (Ticket ID: “CELLSNET-59103”) has been resolved. The fix/enhancement will be included in the next (upcoming) version (Aspose.Cells v25.10), which is scheduled for release in the first half of October 2025. We will notify you in this thread as soon as the new version is available.

8

Thank you very much for the quick fix. That is really helpful.

9

@wwu
Thank you for your feedback. You are welcome. We will notify you promptly once the version v25.10 is released.

10

The issues you have found earlier (filed as CELLSNET-59103) have been fixed in this update. This message was posted using Bugs notification tool by leoluo