Active Cell Grid Selections
From XLCubed v7.2, Grid reports can be driven from the current selected range of another Grid.
We would like one Grid to show a high-level summary of time vs. region, then a second Grid to be driven from the first, showing a breakdown of products vs. month for that region.
- Insert the first Grid, with years on columns and countries on rows:
- From the Grid's right-click menu, choose XLCubed > Properties, then select Interaction > Track grid's active cell. Optionally, also turn on Apply tracking formatting
- Insert a second Grid. Select the Time hierarchy on columns, and then the Active cell option:
- From the Active cell dropdown, select Children:
- Select the desired Product members on columns (Lowest Descendants of Mountain Bikes here)
- Select the Region hierarchy on headers, selecting the Active cell option as before
- The report is complete. Note that clicking on the different data items in the first Grid selects the appropriate members in the second Grid:
Output Row/Column Members to
The selected Grid members will be copied to the selected cells. This allows formulae to be driven from the active selection, for example.
If you have several hierarchies on rows or columns you can select a range and the members of the cross-join will be put into each cell.
After cells have been selected, the selection is immediately changed to this cell. This allows the results to be highlighted, or the worksheet to be changed.
Apply Tracking Formatting
The tracked and currently selected cells are highlighted, as specified on the XLCubedFormats worksheet (see the Tracked Cell and Active Tracking Cell formats).
Track on Member Cells
From v8.1, this option allows you to also track clicks in the members of a Grid as well as the data areas.