This project has moved and is read-only. For the latest updates, please go here.

formula that references another sheet problem

Nov 12, 2014 at 9:14 AM
I am trying to add formulas to a range.
I have two sheets 'sheet1' and 'sheet2'.

I need to set sheet2's formula like this:
sheet2's A1 = sheet1!A1 * 10
sheet2's B1 = sheet1!B1 * 10
...
sheet2's E1 = sheet1!E1 * 10

But result is :
sheet2's A1 = sheet1!A1 * 10
sheet2's B1 = sheet1!A1 * 10
...
sheet2's E1 = sheet1!A1 * 10

My code:
ExcelWorksheet sheet2 = workbook.Worksheets["sheet2"];
sheet2.Cells["A1:E1"].Formula = "sheet1!A1 * 10";
could some one help.
Nov 13, 2014 at 6:18 AM
Hi,
To achieve this you must set the formulas cell by cell.
Nov 13, 2014 at 9:52 AM
Edited Nov 13, 2014 at 9:57 AM
Hi, swmal

Thanks for your response.

Because the formula is dynamic, at last I use below code:
//in formula, sheet1! --> [sheet1]
string formula =  "[sheet1]A1 * 10";

//change "[sheet1]" to "$AAA1*"
Regex reg = new Regex(@"\[[^\[]*\]");
MatchCollection mc = reg.Matches(formula);
foreach (Match m in mc)
{
    string v = m.Value;
    formula = formula.Replace(v, "$AAA" + (m.Index + 1) + "*");
 }

//set range's formula
ExcelRange range = sheet2.Cells["A1:E1"];
range.Formula = formula;

//change "AAA1*" to "sheet1!"
foreach (ExcelRangeBase cell in range)
{
    string cellFormula = cell.Formula;

    foreach (Match m in mc)
    {
        string v = m.Value;
        cellFormula = cellFormula.Replace("$AAA" + (m.Index + 1) + "*",  v.Replace("[", "").Replace(']', '!'));
    }

    cell.Formula = cellFormula;
 }