Functions preceded by "@"

[dfinke]

Discussed in https://github.com/dfinke/ImportExcel/discussions/1424

^{Originally posted by **dmoslander** March 25, 2023} I am having problems assigning the following formula to a Column in a report using 'Set-ExcelRange'.  The formula is added correctly, but there a '@' prepended to the formula.  I found several references to "structural references", but I suspect this is due to the use of newer functions. =TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(IpSubnetVLookup4(B3,NvLsInfo,8),", "),TRUE)) Set-ExcelRange -Range $wsSheet.Cells["F3:F" + $wsSheet.Dimension.rows] -Formula '=TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(IpSubnetVLookup4(B3,NvLsInfo,8),", "),TRUE))' Cell shows #name? result with the formula displayed as: =@TEXTJOIN(", ",TRUE,UNIQUE(TEXTSPLIT(IpSubnetVLookup4(B3,NvLsInfo,8),", "),TRUE)) If I open the XLSM manually and remove the "@", the formula works and shows the expected result. I've been working on this for weeks and finally surrender... I modified the following VLOOKUP example from the repo to verify and get the same result for TEXTJOIN function. try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return} #Get rid of pre-exisiting sheet $xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx" Write-Verbose -Verbose -Message  "Save location: $xlSourcefile" Remove-Item $xlSourcefile -ErrorAction Ignore $data = ConvertFrom-Csv @" Fruit,Amount Apples,50 Oranges,20 Bananas,60 Lemons,40 "@ $xl = Export-Excel -InputObject $data -Path $xlSourcefile -PassThru -AutoSize Set-ExcelRange -Worksheet $xl.Sheet1 -Range D2 -BackgroundColor LightBlue -Value Apples $rows = $xl.Sheet1.Dimension.Rows Set-ExcelRange -Worksheet $xl.Sheet1 -Range E2 -Formula "=VLookup(D2,A2:B$($rows),2,FALSE)" Set-ExcelRange -Worksheet $xl.Sheet1 -Range E3 -Formula "=INDEX(B2:B$($rows),MATCH(D2,A2:A$($rows),0))" Set-ExcelRange -Worksheet $xl.Sheet1 -Range E4 -Formula "=TEXTJOIN(`", `",TRUE,A2:A$($rows))" Close-ExcelPackage $xl

[scriptingstudio]

could not make it work. TEXTJOIN always failed with #NAME? in cell. Excel says, "unrecognized text in formula", but when I click formula bar and then any cell, it worked. weird

BTW. -ArrayFormula parameter can be replaced with leading @ in formula. See vlookup. A leading @ acts as well as -ArrayFormula parameter.

#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message  "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore

$data = ConvertFrom-Csv @"
Fruit,Amount
Apples,50
Oranges,20
Bananas,60
Lemons,40
"@

$xl = Export-Excel -InputObject $data -Path $xlSourcefile -PassThru -AutoSize

Set-ExcelRange -Worksheet $xl.Sheet1 -Range D2 -BackgroundColor LightBlue -Value Apples

$rows = $xl.Sheet1.Dimension.Rows
Set-ExcelRange -Worksheet $xl.Sheet1 -Range E2 -Formula "@VLOOKUP(D2,A2:B${rows},2,FALSE)"
Set-ExcelRange -Worksheet $xl.Sheet1 -Range E3 -ArrayFormula -Formula "INDEX(B2:B${rows}, Match(D2,A2:A${rows},0))"
Set-ExcelRange -Worksheet $xl.Sheet1 -Range E4 -ArrayFormula -Formula "TEXTJOIN(`", `",TRUE,A2:A${rows})"

Close-ExcelPackage $xl -Show

[RobMulder]

I'm having the same problem when using IF() and Text.Merge()

Also problems with "" in the formula.... used backtic before each ". Didn't work. -Formula "=IF('(1) Users'!L42="ja";"ja";"nee")"

[scriptingstudio]

@RobMulder, to avoid backticks you can compose formulas using text formatter: '{0}{1}' -f arg1, arg2

[Eddga]

@RobMulder I haven't tried it in context of the -Formula argument but in general you can escape quotes by using double quotes like so: "=IF('(1) Users'!L42=""ja"";""ja"";""nee"")"

[RobMulder]

@RobMulder I haven't tried it in context of the -Formula argument but in general you can escape quotes by using double quotes like so: "=IF('(1) Users'!L42=""ja"";""ja"";""nee"")"

I tried every combination of quotes and backtics. Nothing works.... I gave up;)

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.