This knowledge base article is work in progress as more information becomes available. Please consider all information contained herein as suggestions and generic tips. Given the broad range of issues with the new Dynamic Arrays feature across installations we nonetheless decided to publish this article.
Since early 2020 Invantive Support receives support questions regarding Excel where Excel formulas are rewritten automatically using the at-sign (“@“) as a prefix. In general, the at-sign often switches appearance when the Excel sheet is opened on another Excel installation.
Also, formulas working previously may break and/or require manual addition of the at-sign. There is yet no uniform description of what “broken“ looks like.
Environment
The at-sign is only introduced on Excel 365 installations. Traditional Office 2016 and Office 2019 don’t introduce the at-sign.
Overall
Starting some Excel 365 builds, the formula language has been changed to make the use of array formulas easier. The at-sign is used to signal the Excel implicit intersection operator.
The at-sign is automatically added to pre-defined formulas where applicable, plus all User-Defined Functions such as from Invantive Control since they can return arrays or scalar values.
The at-sign prefixed to Excel formulas has no formal relationship with table cell references using the at-sign to refer to a specific column and row. It seems however that the at-sign for formulas when used in combination Excel tables seems to break more often.
When Dynamic Array functionality becomes more available, we may take advantage of the new functionality to ease downloads. Till then, Invantive Control sticks on the use of existing array formulas to ensure compatibility with non-Office 365 users.
Pointers
The following articles may provide help. Ultimately please consult Microsoft for any issues:
- Problem description of at-sign on superuser.com.
- Dynamic array tips and tricks.
Excel Tables and Generated Field Expressions
Excel formulas can be generated using Invantive SQL field expressions. Such Excel formulas can be used in Excel tables. Check the presence of the now sometimes necessary at-sign before a cell reference when all of the sudden your Excel expressions evaluates to #OVERFLOW. Add an at-sign when missing and required by your Excel installation.
For example, the following SQL expression generating an Excel expression might generate an #OVERFLOW on some Excel 365 installations:
, '=[Account] & "-" & [Administration Type]' interface_url
It should be changed to:
, '=[@Account] & "-" & [@[Administration Type]]' interface_url
It may be necessary to change back to the original expression when deploying to another Excel installation such as another PC.