Excel "We found a problem with some content" with formula the references table

[LittleRags]

Thank you for creating ImportExcel - it is massively easier to use through PowerShell than using the COM Object.

When I try to insert a formula which contains a reference to a table, using the .Formula method results in an error message about the content. If I use the .Value method, open Excel, edit the cell and immediately Enter, the formula works correctly.
It looks like it is specific to a formula containing a reference to a table and column.
To reproduce:

1. Create a table called InvoiceAmountsin Excel with two columns, InvoiceDate and InvoiceAmount
2. Add appropriate values (I added a range of dates for July 2024 with random amounts
3. Save to a file called c:\temp\InvoiceTest.xlsx and close.
4. Run the following PowerShell:
   `Import-Module ImportExcel
   $InvoicePackage = Open-ExcelPackage -Path "C:\temp\InvoiceTest.xlsx"
   $Worksheet = Add-Worksheet -ExcelPackage $InvoicePackage -WorksheetName "Invoice"

$CellA1 = $Worksheet.Cells[1,1]
$CellA2 = $Worksheet.Cells[2,1]
$CellB1 = $Worksheet.Cells[1,2]
$CellB1.Value = 45479 # corresponds to 6 July 2024
$CellB1.Style.Numberformat.Format = "yyyy-mm-dd"

$CellA1.Value = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"
$CellA2.Formula = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"

$InvoicePackage.Save()
$InvoicePackage.Dispose()`

5. Open the spreadsheet. You will see the error message.
6. After allowing Excel to repair the workbook and on the "Invoice" table edit the formula in A1 and just press Enter without changing anythin.g. The value appears as expected.

I have tried without the leading "=" and get the same result.
It also looks like there is an error returned from Excel, since the process stops making changes after this point, but the error is not returned to PowerShell.

[dfinke]

@LittleRags thanks for using the module!

Do you have a simple script that reprost his?

[LittleRags]

@dfinke The script I set out in point 4 should repro the issue.

[dfinke]

@LittleRags I don't see "InvoiceTest.xlsx" attached. I'd prefer a script + xlsx so I can run and see the issue.

[LittleRags]

@dfinke Ah, I apologise, here is the Excel file, along with the PowerShell code (now that I have figured out how to correctly use the code block)
InvoiceTest.xlsx

Import-Module ImportExcel
$InvoicePackage = Open-ExcelPackage -Path "C:\temp\InvoiceTest.xlsx"
$Worksheet = Add-Worksheet -ExcelPackage $InvoicePackage -WorksheetName "Invoice"
$CellA1 = $Worksheet.Cells[1,1]
$CellA2 = $Worksheet.Cells[2,1]
$CellB1 = $Worksheet.Cells[1,2]
$CellB1.Value = 45479 # corresponds to 6 July 2024
$CellB1.Style.Numberformat.Format = "yyyy-mm-dd"

$CellA1.Value = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"
$CellA2.Formula = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"

$InvoicePackage.Save()
$InvoicePackage.Dispose()