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.
- 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.Worksheet.Cells[“A1”].SetSharedFormula("=IF([@Title]=$B2, False, True)", 10, 1)
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)”.