Dynamically set column in sum formula using aspose.cells

Question

I am use aspose excel export. I am setting formulla in .net.

I want sum of two cells value in third cell. like

excelbook.Worksheets[0].Cells["A2"].R1C1Formula = "=SUM(R1C1,R1C2)";

but i want to set the column dynamically. like in above formulla column C1,C2 are fixed.

now i want pick the column name dynamically , i want to build my formulla like below

excelbook.Worksheets[0].Cells["A2"].R1C1Formula = "=SUM(R1C(Value of Cell B1),R1C(Value of Cell B2))";

so if i enter 1 in B1 cell and 3 in B2 cell then it should sum A1 and C1.i can enter any value in B1 and B2 cell and excel should calculate the sum based upon entered values.

Please suggest how to build this type of formulla.

directly in excel we can do by this way:-

=INDIRECT(B1&1)+INDIRECT(B2&1)

so please suggest can i use INDIRECT in r1c1 formulla or suggest some other way.

Answer 1

If you want to add D1 and E1 in Excel but you want to select the row based on the value of the particular cell; for example, in this case, you might use A1 and A2 to place the value of the row to select, then you will use the formula like this:

=SUM(INDIRECT("D"&$A$1),INDIRECT("E"&$A$2))

You can assign the same formula using Aspose.Cells for .NET as well. Please see the following code:

        //create XLS File
        //Instantiate a Workbook object that represents Excel file.
        //create a workbook
        Workbook CellsDocument = new Workbook();
        //Note when you create a new workbook, a default worksheet
        //"Sheet1" is added (by default) to the workbook.
        //Access the first worksheet "Sheet1" in the book.
        Worksheet sheet = CellsDocument.Worksheets[0];
        //Access the cells and put values
        Aspose.Cells.Cell cellA1 = sheet.Cells["A1"];
        cellA1.PutValue(1);

        Aspose.Cells.Cell cellA2 = sheet.Cells["A2"];
        cellA2.PutValue(1);

        Aspose.Cells.Cell cellD1 = sheet.Cells["D1"];
        cellD1.PutValue(4);

        Aspose.Cells.Cell cellE1 = sheet.Cells["E1"];
        cellE1.PutValue(5);

        Aspose.Cells.Cell cellE14 = sheet.Cells["E14"];
        cellE14.R1C1Formula = "=SUM(INDIRECT(\"D\"&$A$1),INDIRECT(\"E\"&$A$2))";


        //Save the Excel file.
        CellsDocument.Save("output.xls", Aspose.Cells.SaveFormat.Excel97To2003);

Please try it at your end using the latest version of the Aspose.Cells for .NET. If you think it doesn't help or I'm unable to understand your question clearly then please share your further thoughts.

Disclosure: I work as a developer evangelist at Aspose.