Using OpenXML, my VLOOKUP formula doesn't work, until the user manually edits the cell

Question

The sequence of events I am trying to create is this:

1. My OpenXML app generates a new Excel file, populating it with data from a database.
2. User opens the file and goes to the Order sheet.
3. User makes a selection from the Customer dropdown.
4. On the Product sheet, there is a VLOOKUP, which checks the Customer drop down and displays optional special products, based on the customer name selected.
5. Back on the Order sheet, the Products drop down shows the customised list of products.

Formula I am inserting:

string theFormula = "IFERROR( IF(VLOOKUP(CustomerNameRange, CustomerTable, 8, FALSE) = 0,\"\", VLOOKUP(CustomerNameRange, CustomerTable, 8, FALSE)),\"\")";

Code I am using to do the insert:

    Row r = new Row();
    r.RowIndex = (UInt32)index;

    Cell c = new Cell();
    c.CellReference = index;
    c.DataType = new EnumValue<CellValues>(CellValues.String);

    c.CellFormula = new CellFormula(theFormula);
    c.CellFormula.CalculateCell = true;
    r.AppendChild(c);

Code I am using to force the sheet to recalc:

CalculationProperties prop1 = workbookPart1.Workbook.GetFirstChild<CalculationProperties>();
prop1.ForceFullCalculation = true;
prop1.FullCalculationOnLoad = true;
prop1.CalculationMode = CalculateModeValues.Auto;
prop1.CalculationCompleted = false;

However, none of this extra recalc code seems to be working. The special product VLOOKUP doesn't recalc successfully, until the user manually edits that cell. How can I force Excel to treat the inserted formula cell like normal?

I understand that OpenXML doesn't and isn't meant to do the recalc (it's a document editor, not a fully-fledged spreadsheet engine).

Answer 1

Resolution was to not programmatically enter the formula, but to have the formula permanently on the sheet.