Copying a range across workbook which is being referenced within that workbook is causing #ref error

Aspose.Cells Product Family
21

will this carry over the number format as well?

22

@Adhirath
res.xlsx.zip (22.1 KB)

If I copied “originalRange” range from OriginalTableWkbk.xlsx to “copiedRange” in CopiedTableWkbk.xlsx with MS Excel, the table does not exist .(see attached file)

1 Like
23

@Adhirath
If you want to extend the table, please use ListObject.PutCellValue() method to set value.

24

sure. I had one final requirement. How do I set the cell value along with the number format. So in the original range if I change the number format of a cell I want that to reflect in the copied range. How can I do that?

25

@Adhirath
If you want to set the formula, please use ListObject.PutCellFormula() method.

1 Like
26

sure. I had one final requirement. How do I set the cell value along with the number format. So in the original range if I change the number format of a cell I want that to reflect in the copied range. How can I do that?

27

@Adhirath
In Excel， if we change number format of the cell “A3” in the worksheet “copied”, the format of “A4” in the worksheet “referenced” does not change. So you have to change the number format in the worksheet “referenced”.

And you can try camera feature of MS Excel， then image could reflect any change of the range.

28

that works if I want to show the changes in the referenced sheet, what I want is copy over the number format of the original range to the copied range not copied range to referenced range. If I simply use copiedRangecell.Value = originalRangecell.Value I dont get the number format only the value which could show something completely different in the case of date or currency.
Also I am doing it cell by cell as provided in this code-
for (IEnumerator ie = destinationRange.GetEnumerator(); ie.MoveNext():wink:
{
Cell cell = (Cell)ie.Current;
cell.Value = null;
}
for (IEnumerator ie = originalRange.GetEnumerator(); ie.MoveNext():wink:
{
Cell cell = (Cell)ie.Current;
destinationRange[cell.Row - originalRange.FirstRow, cell.Column - originalRange.FirstColumn].Value = cell.Value;
}

So how do I copy the cell but without the formula?

29

@Adhirath
Please set value and style as the following :

Cell destCell = destinationRange[cell.Row - originalRange.FirstRow, cell.Column - originalRange.FirstColumn];
destCell .Value = cell.Value;
destCell.SetStyle(cell.GetStyle(false));
30

Bless you man.

31

@Adhirath,

You are welcome. If you have any additional questions or feedback, don’t hesitate to reach out to us.

1 Like
32

@Adhirath,

ThWe are pleased to inform you that your issue (Ticket ID: CELLSNET-58749 - Invalid referred array formulas when table was removed) has been resolved. The fix/enhancement will be included in the upcoming release of Aspose.Cells (v25.8), scheduled for the first half of August 2025. You will be notified once the new version is published.

33

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