Forum Discussion
Excel Dynamic Formula
I added a formula to a cell using EPPLus C# as below.
mySheet.Cells[rowNumber, columnNumber].CreateArrayFormula"UNIQUE(FILTER({sheetName}!C:C,{sheetName}!B:B = A{rowNumber}))"));
When I open the generated xlms it shows additional curly braces around the function like below
and data is not populated from the formula.
But when I click on the formula-bar these curly braces disappear and values are populated.
You CreateArrayFormula, my guess it's the same as manually add formula with Ctrl+Shift+Enter. It will be with curly braces and returns only first element of dynamic array.
Try to create regular formula, not array one.
thanks for your response.
I tried with regular formula as well and then it comes with an additional @ sign which I need to delete manually to get the data populated.
Here is my code
var firstColumnFormula = $"UNIQUE({tailNumber}!B{firstDataRow}:B{lastDataRow})";
calculatedDataSheet.Cells[currentRow, currentCol].Formula = firstColumnFormula;and output is
=@UNIQUE(N175QS!B15:B599)Try to use .Formula2 instead of .Formula. The latest assumes single value returned, thus implicit intersection operator ("@") is added.
