Recently a customer raised a support issue asking if it was possible to highlight a row within a table based on a selection made in a chart.
Interactive charting being a core XLCubed feature, of course it is!
Here we can click on a country to highlight the relevant rows or we can go one step further a select a product within a country on the chart and have only that particular row be highlighted.
Achieving this requires 3 steps:
- Setting the chart to output the selection into a cell
- Formatting the grid to respond to the chart selection
- Forcing a refresh of the grid when the chart changes
Setting the chart to output selections
Easily achieve through the dedicated XLCubed feature, setting the chart to output selections made is as easy as a couple clicks to get to the chart properties. Open the properties menu, then simply click on the tick next to the part of the chart you would like output and select where in the workbook it should appear.
Once setup, when a selection is made on the chart if the relevant part is ticked in the menu then that member will be populated inside the defined cell.
Here, the column (country) and the category (products) selections will appear in cells B2 & B3.
Set the grid to pick up the chart output
XLCubed advanced grid formatting controls gives users flexibility to control the visuals of their reports just how they want it.
Here we want to apply a background fill to have the row stand out.
The formatting which we applied has been added as a new row on the format sheet, and we can now make additional changes there, namely, to change the member from a static selection to a dynamic one by using the power of Excel flexibility.
To do this, we simply use an Excel formula to link the member controlling the formatting to the cell where the chart selection is output.
With this changed, the grid will now format the row containing the chart selection on refresh.
However one additional step is needed as a click on a chart will not cause a grid to refresh by default. We can make the grid refresh using two useful XL3 formulae: XL3RefreshObjectsNamed() & XL3Link()
Set the grid to refresh where the chart selections are done
First let’s set up the XL3Link, we will use it to trigger the refresh via the XL3RefreshObjectsNamed() function. XL3Links are easily inserted through the wizard found in the ribbon.
In this case, when activated the XL3Link will now input the value TRUE into cell B5.
This will in turn activate the refresh formula once it is set up.
Simply type in “=XL3refreshObjectsNamed(” then select cell B5 as the first argument then the name of the grid as the second argument. The first argument is the trigger, as soon as the formula sees TRUE here, it will activate and then turn the trigger to FALSE to avoid activating again.
The second argument just allows us to select which elements we would like to refresh to avoid refreshing the entire workbook.
In this case, we have: “ =XL3RefreshObjectsNamed(B5,”GridA”) ”, so when cell B5 contains TRUE, GridA will be refreshed. The last step is the set the chart to activate the XL3Link when a selection is made via the same properties menu used to set the outputs.
So once a selection is made within the chart, 2 things will happen. The selection will be output to cells B2 & B3 which is then picked up by the format sheet and the XL3link will be activated. This will in turn output TRUE to the trigger causing the refresh formula to activate which will refresh the grid while taking into account the changes to the format sheet.
This is but one example of how different XLCubed elements can be mixed to make sure you achieve your reporting needs. What are the top combinations you’ve found?
