FormulaArray inserted from C# API only displays the first item in array until array formula is edited and recalculated

Question

Background: I'm using the C# .NET COM API to retrieve data from a source using an API, then loading that data into a spreadsheet using the .NET Excel interface. I'm also using the Excel interface to create a "View Sheet", which I have conditional formatting, data validation, index match formulas etc. I'm using .NET 8.0 and Excel version 16.0

My problem is with array formulas. In the LOADSHEET (where I dump the raw data from the API), I have text separated by a comma delimiter.

Cell [B2] has Data Validation as follows:

Excel.Range range = (Excel.Range)newSheet.Cells[2, 2];
range.Validation.Delete();
range.Validation.Add(
    XlDVType.xlValidateList,
    XlDVAlertStyle.xlValidAlertWarning,
    Formula1: "=INDIRECT(\"LOADSHEET!$A$2:\"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))"
);
range.Validation.ErrorTitle = "Value Error";
range.Validation.ErrorMessage = "Please select a value from dropdown list.";
range.Validation.ShowError = true;
range.Validation.InCellDropdown = true;
range.Validation.IgnoreBlank = true;

Then the formula in D6 is:

newSheet.Cells[6, 4].FormulaArray = "=(TRANSPOSE(TEXTSPLIT(INDEX(INDIRECT(\"LOADSHEET!$D$2:\"&
    ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1)), MATCH(B2, INDIRECT(\"LOADSHEET!$A$2:\"&
    ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))), 4), \",\")))";

In excel that puts

{=TRANSPOSE(TEXTSPLIT(INDEX(INDIRECT("LOADSHEET!$D$2:"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1)), MATCH(B2, INDIRECT("LOADSHEET!$A$2:"&ADDRESS(COUNTA(LOADSHEET!$A:$A)+1,1))), 4), ","))}

When the spreadsheet is saved and closed, there is no pre-selected value in B2. There is no formatting done to E6 or D6 besides setting them to left horizontal align, and setting the column width.

At first I tried adding event handlers using Sheet.Change += Change_Handler

Sheet.Change += Change_Handler;

private static void Change_Handler(Excel.Range target)
{
    if (target.Address == "$B$2")
        target.Worksheet.Calculate();
}

This didn't change anything.

I tried using various different ways of setting the value, such as:

newSheet.Range["D6"].Value2 = [Formula];
newSheet.Cells[6, 4] = [Formula];

Both of these didn't work, except instead of the formula being wrapped in curly brackets {}, there was an '@' symbol before the TRANSPOSE formula.

Source Data:

Name	Client	Client-ID	Values1	Data1	Data2	Client Type	Instance Type	Values2	Last Updated
Internal - Inactive - 2/27/2024	Internal	10	RANDOM_VALUE,OTHER_RANDOM_VALUE	0	0	Inactive	Inactive	ANOTHER_RANDOM_VALUE,FINAL_RANDOM_VALUE	27/02/2024

Current output:

Values1	Values2
RANDOM_VALUE	ANOTHER_RANDOM_VALUE

Expected output:

Values1	Values2
RANDOM_VALUE	ANOTHER_RANDOM_VALUE
OTHER_RANDOM_VALUE	FINAL_RANDOM_VALUE

It's worth noting that the formula does work if you edit the formula (dont change it) and press enter while in the Excel Desktop Client