Invalid formula in cell

Aspose.Cells Product Family
1

When I try to set a R1C1Formula on cell R21 that looks like this =SUM(R13:R20) I get an error: Invalid formula in cell R21: =SUM(R13:R20). I use the same type formula on many other places in the worksheet and this is the only one that fails. I have no problem doing it in the worksheet, only via aspose.excel.

Why does this error occur?

2

Which version of Aspose.Cells are you using? I tried following code and it worked fine:

Workbook wb = new Workbook();
wb.Worksheets[0].Cells["R21"].R1C1Formula = "=SUM(R[-8]C:R[-1]C)";
wb.Save("d:\\test\\abc.xls");

Workbook wb = new Workbook();
wb.Worksheets[0].Cells["R21"].Formula = "=SUM(R13:R20)";
wb.Save("d:\\test\\abc.xls");

3

Try the excel document attached, and try this code:

Aspose.Excel.Excel excel = new Aspose.Excel.Excel();

excel.Open(@"c:\temp\report.xls");

Aspose.Excel.Worksheet ws = excel.Worksheets["Sheet17"];

ws.Cells["R21"].R1C1Formula = "=SUM(R13:R20)";

I use Aspose.Excel version 3.6.2.0.

4

Cell.R1C1Formula property is used to set formulas in R1C1 style. So if you want to use it, your code should be:

cells["R21"].R1C1Formula = "=SUM(R[-8]C:R[-1]C)";

And you can use Cell.Formula property to set formulas in A1 style:

cells["R21"].Formula = "=SUM(R13:R20)";

5

Hi,

I face a similar problem. I am applying a simple SUM formula in R1C1 type. I loop through a set of columns and apply this formulua to each cell. It totally has 20 cells. The formula gets applied for 15 cells but fails in the 16 cell giving Invalid formula in cell error.

Please let me now if it is a logic problem that i need to look into.

Thanks

Aneetha.

6

Hi Aneetha,

Could you elaborate and give us more detail. Kindly create a sample console application using your template file that can product the issue you are mentioning, zip it and post it here. So that we may check and resolve it soon.

And also pleae try with the latest version of Aspose.Cells(4.3.)

Thank you.