Functions preceded by "@"

[dmoslander]

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

[dfinke]

create an issue #1425