With Listqueries it is possible to populate dynamically a defined output area of cells with a list of elements that are queried directly from your datamodel. The output area of a listquery usually is used in many cases as input for CELLS01 value queries.
Listqueries can be used to enrich your workbook with dynamic structures like:
- Dynamic List of customers in a sheet
- Dynamic List of vendors in a sheet
- Dynamic List of accounts in sheet
The most important
Named Ranges for the configuration of the Listquery (optionally)
- data1_LISTQUERY01_T_DIM001_CmdFilter
- data1_LISTQUERY01_T_DIM001_CmdSort
- data1_LISTQUERY01_T_DIM001_CmdGroupBy
Named Ranges for the configuration of a dynamic Listquery (optionally)
- data1_LISTQUERY01_T_DIM001_CmdDynamic
- data1_LISTQUERY01_T_DIM001_CmdExpansion
Please be aware that dynamic list queries cannot combined with dynamic tables (data1_TABLE01_T_DIMxxx) in the same workbook since.
Named Ranges for the Output Area of the Listquery (overview):
- data1_LISTQUERY01_T_DIM001_KEY (array format)
- data1_LISTQUERY01_T_DIM001_NAME (array format)
- data1_LISTQUERY01_T_DIM001_A001 (array format)
- data1_LISTQUERY01_T_DIM001_A002 (array format)
- etc.
How to define the control parameters
data1_LISTQUERY01_T_DIM001_CmdFilter (optional)
(to be described)
data1_LISTQUERY01_T_DIM001_CmdSort (optional)
(to be described)
data1_LISTQUERY01_T_DIM001_CmdGroupBy (optional)
(to be described)
data1_LISTQUERY01_T_DIM001_CmdDynamic (optional)
- 0 = Additional rows/columns are not created dynamically = default
- 1 = The named range is dynamically shrunk or expanded by rows or columns. The first row or column is copied/pasted to the new row(s)/column(s) so that the formulas and conditional formatting are retained and automatically adapted to the new row/column (behaviour as in MS Excel).
data1_LISTQUERY01_T_DIM001_CmdExpansion (optional)
- row = data is added row by row =default setting
- column = data is added column by column
How to define the output parameters
(to be described)