I am trying to run an “=FILTER(…)” formula created in a PowerShell script that should spill over and use additional rows and columns. But it is not. Instead I get “=@FILTER(…)” in the cell.
I can manually remove the “@”, press enter, and the formula works.
I actually don’t know how this is related to each other.
I’m unsure if I get this. Is the file you’re talking about a CSV file or XLSX file? CSV files do not support Excel formulas. It might be possible to calculate the result you’re after in PowerShell as well.
Sharing more of your code may help undersanding your issue.
But have you ever worked with an Excel formula like FILTER() or XMATCH() in PowerShell? I think there is something about them “spilling over” to the other rows and columns that is not working with my PowerShell code.
No one has asked you to share your data. Rather, your code that you’re claiming is causing the issue. Without being able to reproduce and test in our own it would just be a bunch of guessing.
Functions that return multi-cell ranges or arrays (which FILTER can) will be prefixed with ‘@’ if they were authored in an older version of Excel (which, I’m guessing, includes the COM object).
From looking at Get-Member on the cell. There’s a second property Formula2 that stores the function with the ‘@’ included.
Obviously you’ll need to test to see if that breaks anything and read the ’ Can you remove the @?’ section in the linked document to understand the implications.
Yes. Thank you. That fixed it, using .Formula2. And the data does spill over to the neighboring cells, just as described in the “Can you remove the @?” section of the link you sent.