255-character limit for arguments of a function e.g. SI.QUERY()

Applies to:

• Velixo NX
• Sage Intacct

Overview

Certain functions such as SI.QUERY() that includes arguments that expect a concatenated list of column names (such as: Column or OutputNames) can easily reach Excel's character limit, and throw an error.

Cause

Microsoft Excel has hard limits on:

• the number of characters in a formula: 8,192 characters
• the number of characters of an argument of a function: 255 characters

Resolution

There are no resolutions other than a workaround, as those errors are built-in character limitations of Excel.

Workaround

A simple workaround for the argument character limit is to reference a different cell for any argument of a function that causes the error.

Example

The below example uses an Si.QUERY() function in Cell A5, that references:

• Cell B3 for the Object argument
• Cell B1 for the Filter argument
• the below long string of column names in Cell B2 for the Columns argument

PROJECTID,NAME,DESCRIPTION,PROJECTCATEGORY,PROJECTSTATUS,BEGINDATE,ENDDATE,ACTUALQTY,APPROVEDQTY,REMAININGQTY,MANAGERID,MANAGERCONTACTNAME,RECORD_URL