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.
So, why do I get the ‘@’?
$a = 2
$b = $rec_count
$c = (import-csv $File).count + 1
$ExcelWorksheet.cells.Item(2,3).Formula = ‘=FILTER(ALL!A’+$a+‘:K’+$b+‘,ISNUMBER(XMATCH(ALL!G’+$a+‘:G’+$b+‘,“CLOSE”)+XMATCH(ALL!E’+$a+‘:E’+$b+‘,A’+$a+‘:A’+$c+‘)+SEARCH(“auto closed duplicate”,ALL!C’+$a+‘:C’+$b+‘)))’
Any help and/or advice would be greatly appreciated.
Welcome to the forums.
Using the great module from Doug Finke ImportExcel might be helpful in your case.
If that’s not possible for any reason you may share a little bit more of the code you’re using and maybe a little more context of your task.
Thanks I have looked at that module, but we have Excel, and I am still rather new to this.
And this is a work in progress so, for this page I have the above and the ALL page is an imported CSV file.
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.
I am talking about an XLSX file. And I am working on a blank sheet for this page.
OK but you have the code line
$c = (import-csv $File).count + 1
in your code!? What about this?
In the =FILTER() formula, $c is used in the XMATCH() formula for the last row number of the list: XMATCH(ALL!E’+$a+’:E’+$b+’,A’+$a+’:A’+$c+’).
The =FILTER() formula works when entered in manually. But not when entered through PowerShell. It puts that @ right after the = and I do not know why.
I am trying to reproduce your situation to be able to help you. At the moment I am unable because I don’t have all needed information.
I’m sorry, I cannot share my data.
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.
I’m not asking for your actual data. Can’t you share some sample data?
The @ is the implicit intersection operator.
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.
PS E:\Temp> $worksheet.Cells.Item(10,4).Formula = '=A1:A5'
PS E:\Temp> $worksheet.Cells.Item(10,4) | Select Formula,Formula2
Formula2 property instead of
Formula sets the function without inserting the ‘@’.
PS E:\Temp> $worksheet.Cells.Item(10,5).Formula2 = '=A1:A5'
PS E:\Temp> $worksheet.Cells.Item(10,5) | Select Formula,Formula2
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.
Thank you so much!