Formula issue when trying to set formula through a whole formula column

Hi,

We have come across an odd issue. We are using an input file with a known named table. We aim to fill this input file’s table with data, but we are having trouble making sure that the formula that is present in the input file is extended through the whole formula column. The table only has 2 data rows which are both empty except for the formula column. Our code fills the table using a custom method, that re-sizes the table, populates it before finally copying the formula of the formula column to the rest of the formula column.

An example of what we are trying to achieve would be to have a formula such as “=IF([@Title]=$B2, False, True)” we would like this formula to be extended through the whole formula column as “=IF([@Title]=$B3, False, True)” -> “=IF([@Title]=$B4, False, True)”, etc.

I can see that something like Worksheet.Cells[i].Copy(Aspose.Cells[0]) will unlikely update the cell reference as above.

Question: Is there a way to copy a formula in a formula columns if this input file’s table increases its number of rows that will also update the cell reference accordingly?

I have attached a small project to demonstrate the issue. I have also attached a input file “input for formula test” for you to use. The input file contains a table with a formula column.

Additional Information:
- I have tried using Worksheet.Cells.SetSharedFormula(), this did the trick for a smaller formula. However, when this method is used with the IF formula mentioned above, it reached a deadlock.

Worksheet.Cells[“A1”].SetSharedFormula("=IF([@Title]=$B2, False, True)", 10, 1)

- A weird case is that we have one input file that was showing a different result from the rest; it was somehow updating the formulas cell references…but the cell reference it is setting are incorrect.
I have also attached the file that was showing this issue “Output with incorrect formula.xlsx”. The “Formula3_F” was the something like “=IF([@Title]=$B2, False, True)”.

Hi,


Thanks for the sample project and template file.

After an initial test, I am able to observe the issue as you mentioned by running your sample project with your template file. The formula in the list object/ table cells is not auto-extended accordingly in the output files. I have logged a ticket with an id “CELLSNET-42092” for your issue. We need to thoroughly investigate the issue on our end if this is an issue or expected behavior. It is possible that we might provide you with updated codes for your requirements. We will figure it out soon.

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

Thank you.

Hi,

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

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

The issues you have found earlier (filed as CELLSNET-42092) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.