Last week’s post looked at the fundamentals of formatting XLCubed grids. A lot can be done with these techniques but sometimes more interactive formatting is desired. Within an XLCubed grid you can set Interaction Options to track which cell has been selected and output the selection and/or format it. In this post, we will look at how to apply this to highlight a row across multiple grids.
- Three grids, all with the same members on rows
- One of these is a ‘master’ grid – clicking on a cell in the master grid will update selections in the dependent grids
- Clicking on a cell in the master grid will highlight the row in all three grids
Track active cell
The first step is to track the active cell in the master grid. This is turned on in the grid’s properties, underneath the Interaction tab.
In our example, the master grid outputs its column members (the date hierarchy) to a cell which acts as a filter for the other two grids. To create row highlighting though, we need the grid to output the active row member. We also turn on “Track on member cells” so that if the member name itself is selected (rather than one of the values in the grid), the highlighting is still applied.
Format the selected member
Now we have the selected row member, we can reference this in the format sheet in order to apply a highlighting to the relevant member.
Tip: to make sure you enter the correct hierarchy and member names in the format sheet, first use the right-click menu directly on the grid to format any member. You can then go to the format sheet where a new formatting rule will have been written, and edit this as necessary.
In this example, Product Categories would be entered for the hierarchy. The member cell then references the output from the active cell in the master grid.
Refreshing the grids
New formats are applied to a grid after it has been refreshed. So you may be clicking around your grid wondering why you’re not seeing anything change. If you “Refresh all” grids from the XLCubed ribbon then you will do.
Having to perform this additional action is of course not ideal. We overcome this by activating an XL3Link after the active cell changes, which will in turn trigger a refresh formula.
First, set up your formulae to something similar to the below.
Then, under the Interaction tab in the grid properties of the master grid, turn on “Activate XL3Link” and reference the link just created – in this case ‘Outputs’!$C$4.
When this link is activated, it puts the value TRUE into cell D4. This cell is the trigger for the XL3RefreshSheetObjects formula, which will then refresh all grids on the relevant sheet before setting the cell D4 back to FALSE.
This will refresh all grids on the specified sheet which of course may take some time if the grids are large. You could use XL3RefreshObjectsNamed to refresh only the relevant grids.
You will now see whenever you select a cell in the master grid, the row is highlighted along with the correct row in the two dependent grids.
To apply the row formatting to only a selected number of grids, you can specify the item name in the format sheet. In order to only have to write one rule for all the necessary grids, give the grids a common name such as FormattedGrid1, FormattedGrid2, …. etc. and then use a wildcard in the item name column in the format sheet, i.e. FormattedGrid*.
This can be a great way to provide some interactivity and clarity to an otherwise difficult to read report. As always, let us know your thoughts and feedback!