SOLVED

Excel XMATCH and FILTER with PowerShell

New Contributor

I am trying to use the FILTER and XMATCH formulas with a PowerShell script but when I open the XLSX files, those formulas are not spilling over to the other rows and columns and have '@' in them.   

 

If I manually remove the '@' and press enter, the formulas work, spilling over and populating the other rows and columns as set.   

 

Is there something extra I have to do within my PowerShell script?  I have tried using the .Formula, .FormulaArray, and .Value() lines.

 

Any advice would be much appreciated.

2 Replies
best response confirmed by JCannon2021 (New Contributor)
Solution
You need the Formula2 property of the Range object.

@Jan Karel Pieterse 

 

Awesome.  Much thanks.

 

This is working for me now :

 

$ExcelWorksheet.Cells.Item(2,3).Formula2 = '=FILTER(...)'