c# - Excel.Worksheet.Cells[row,col] = "=Formula" vs. Range.set_Value(Missing.Value, arrayFormulas) -

excel.worksheet.cells[row,col] = "=formula / reference"  

while in above excel updates formula / reference , shows result in datasheet, in code below, when using range.set_value(..) datasheet not updated @ all

string[,] myformulas = new string[nrrows, nrcolumns]; ... myformulas [x,y] = e.g. "=sum(d1:d" + rownr + ")"; myformulas [x,y+1] = e.g. "=table1!a1"; ... range.set_value(missing.value, myformulas) 

and shows formula string, e.g. =table1!a1.

i cannot make update. neither calucalteall(), nor refreshall(), nor anyhing. suggestions how achieve update in datasheet?

edit : can set entire array single statement range.set_value(missing.value, myformulas). question how make excel evaluate formulas in array (and not treat them simple strings, or setting cells 1 one excel recalculates.)?

i found rangevariable.formula = rangevariable.value translate 'formula text' bona fide excel formula, , done against cells in range.

worksheet sheetone = ... int numberofrows = 5000;  // make massive range on sheet 1, use cell assignment array fastness range range = sheetone.range["a1"]; string[,] links = new string[numberofrows+1, 1]; range = range.resize[numberofrows+1, 1];  (int count = 0; count < numberofrows; count++) {     // build =hyperlink formula set text in each cell     string worksheet = "sheet2";     string cellref = string.format("a{0}", count + 1);     string formula = string.format("=hyperlink(\"#{0}!{1}\", \"{2}\")", worksheet, cellref, string.format("hyperlink number {0}", count));      links[count, 0] = formula; }  //range.set_item(type.missing, type.missing, links); range.set_value(type.missing, links) // heinrichstack range.formula = range.value; //<--- boom baby, 'formula text' turns bona fide excel formula 

hope helps