Forum Discussion
Issue _xlfn.Single and @ being added to formulas - unsupported functions - NOT THE REASON
Excel has recently been adding the @ symbol after saving and reopening the spreadsheet.
Examples
=@NamedRange
=@Iferror(...
and even inside the formula
=@iferror(index(@indirect(file.xlsx...
When my spreadsheet was opened by a colleague if had _xlfn.Single(sSourceFile)
This was a formula referencing a Named Range called sSourceFile. This was not a function that is not supported by my version of Excel; it happened to a simple reference to a Named Range. My colleague has Excel 2016 and I have MS Office 365.
This issue began after MS Office 365 was updated.
Has anyone else had this issue and has anyone found a work around or resolution?
Thank you,
Katherine Bradford
4 Replies
Katherine, yes, so called implicit intersection '@' was introduced with dynamic arrays in previous year. All details are here Implicit intersection operator: @
Usually it works correctly and could be ignored. An error you have most probably since you used mixed operator like =SingleSource + something. Such errors are also described in above article.
The only way is to re-write such expression differently.
I have come across this issue a few times over the last few months, and after a quiet spell it re-occurred today. I sent a workbook to a client who is on exactly the same version of excel as me (2011 13426.20332). 3 index formula that I had written (without the implicit intersection '@'), had been automatically changed by excel so that they now had the @ symbol. When I sent the file to my client, on opeing the file that @ symbol had changed to _xlfn.single and so was now returning the #NAME? error.
To fix the issue, I must go back into my copy, delete the @ symbol, and save&send again.
This surely must be a bug in Excel, under the circumstances.
- KLBradford and StuartBisset
I ran across this. The dilemma is having a cross platform where an older version of Excel that neither recognizes the implicit operator ("@") nor is it using Indirect as a dynamic array but as a dynamic lookup of variable coordinates. Excel 365 update "infects" with curly braces for "automatic" CSE (Ctrl-Shift-Enter) application to a non-array. Then tries to "fix" it by inserting an implicit operator everywhere it thinks is not supposed to be an array. When opened in an older version, the command path to the Library (_xlfn.single) cannot be found. So, it keeps coming up as an error in older versions.
See documentation - https://techcommunity.microsoft.com/t5/excel-blog/preview-of-dynamic-arrays-in-excel/bc-p/2450301#M2213 and,
'https://techcommunity.microsoft.com/t5/excel/workaround-needed-for-cross-platform-dynamic-array-formula/m-p/2460351#M103647
- Thank you so much. One formula was just a reference to a named ranged. We have a consultant with the same version of MS Office 365. He is not experiencing the issue at all I will read the article and let you know. Thank you again!!! Katherine Bradford
