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

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

CODE

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