Hello,
I have 2 sheets and I want to copy formula from sheet1’s cell into sheets2’s cell.
ex.
Sheet1:
sheet1CellFormula = “=A10”;
and If I copy this formula into sheet2 cell, currently sheet2 cell formula is getting copied like this: “=A10” (which is incorrect, this way it is referring to A10 cell of sheet2 not sheet1),
It should refer to A10 cell of Sheet1 i.e. expected formula is =“Sheet1!A10”
Please suggest how to achieve this.
@ranishinde,
In MS Excel, when you perform the copy cell/formula from one sheet to another sheet manually, it works the same way. For your needs, you may try as following sample code:
e.g.
Sample code:
Workbook workbook = new Workbook();
//Get the first sheet (default)
Worksheet worksheet = workbook.Worksheets[0];
//set formula
worksheet.Cells["A1"].Formula = "=A10";
//Add another sheet to workbook
Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
//copy formula
sheet2.Cells["A1"].Formula = worksheet.Cells["A1"].Formula.Insert(1, worksheet.Name + "!");
workbook.Save("e:\\test2\\out1.xlsx");
Hope, this helps a bit.