1. Calculation functions
  2. All Functions
  3. Miscellaneous Functions

You can use the COLLECT() function to return a result when a line item subset is used as a dimension. A line item subset is a selection of line items from one or more modules. It has all the properties of a normal list and can be:

  • reused in other modules; or
  • used in a list-formatted line item to create a drop-down list.

The function collects the original line item values from the source module or modules that you selected for your line item subset.




Input Format Output Format
Number Number


The function requires no arguments.


The function has the following constraints:

  • Parameters: Can only be used as a stand-alone function taking no parameters and always appearing as COLLECT().
  • Usage: Cannot be used as part of a conditional formula.
  • Source Module: The source module or modules must contain the original line items that were selected for the line item subset used in the result module.
  • Result Module: The result module must have a line item subset as a dimension.

Excel equivalent

  • No Excel equivalent

Back to Top


In this example, a line item subset has been added to the model:

The Receipts and Payments line item subsets has original items selected from the P&L, Gross Margin, and Fixed Assets modules.

In the result module, the line item subset is used as a dimension:

The COLLECT() function is used against the line item subset items as part of the cell formulas:

COLLECT() * 'Inc/Expse Flag'.'Inc/Expse Flag'

The formula uses COLLECT() to collect the original data values from the P&L and Fixed Assets source modules and then multiplies collected values by a plus or minus flag (for either income value or cost value), where the flag value is drawn from another source module:

And in this example, the original Sales item value from the P&L module for London in Jan '11 of 105,000 has been multiplied by a positive flag to be cast as an income value. In contrast, Staff Costs, after collection from the Gross Margin module, is multiplied by a negative flag and so is cast as a cost value.

Back to Top

Similar functions