Forum Discussion

nalinkodithuwakku's avatar
nalinkodithuwakku
Copper Contributor
Sep 23, 2024

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 ge...
Excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Sep 23, 2024

nalinkodithuwakku 

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. 

  • nalinkodithuwakku's avatar
    nalinkodithuwakku
    Copper Contributor
    Sep 23, 2024

    SergeiBaklan 

    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)

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Sep 23, 2024

      nalinkodithuwakku 

      Try to use .Formula2 instead of .Formula. The latest assumes single value returned, thus implicit intersection operator ("@") is added.

Resources