How’d you like that….displayed!

Today’s blog will show you a really quick and easy way to format your grid to show different display units.

This approach is ideal for dynamic Grids where the size of the values can vary considerably based on the selected filters, or where the user has drilled down to lower levels in the data. For example, if country level numbers are in hundreds of millions, but customer level numbers are in hundreds or thousands, it can be useful to have the ability to quickly change the display units.

Using this method, you can switch quickly to using different formats.  In our example we want to give the user the option to display the measure Reseller Sales Amount as Units, Thousands or Millions.

You can see we have a slicer to the right of the grid giving the user the choice of how to display the figures.

The slicer is based on an Excel range and is not directly linked to the grid.

A couple of things to note are the ‘Update range’ and ‘Activate XL3Link’ slicer settings are checked but more of that later.

‘Enabling Update Range with selection’ means that the slicer choice is written to a cell in the workbook – in our example it’s cell $M$17 (shown in red in screenshot below) – currently Millions are selected.

The other thing to note from the screenshot above are N13:N15.  These are a series of IF statements like below which will determine how the format sheet is updated:

=IF(M13=$M$17,”*”,”NOT SELECTED”)

As M13 is not equal to M17 the Units  row will be set to ‘NOT SELECTED’.

The row containing the ‘*’ is the format which we want to be applied to the Grid.

We now need to reflect that in the format sheet. This is done by first adding three rows for the relevant hierarchy, and setting the Excel numeric format column G to units on the first row, and then thousands and millions on the other two.

We can then set the ‘Member’ cells in column E as a simple formula referencing the table shown above. For example, the Unit’s member incell E50 in the XLCubedFormats sheet is set as =Sheet1!N13.  The same process is followed for Thousands and Millions.

With the format sheet as shown above, based on the user’s slicer selection, all members will then be displayed with the predefined Millions format as ‘*’ is a wildcard and will match on all the members in the Product Categories hierarchy.

The formatting for Units and Thousands will not be applied – unless of course you have a member in your hierarchy called ‘NOT SELECTED’!

As mentioned previously, our Excel slicer is not directly linked to the grid – we need a way to tell the grid to refresh each time the slicer choice changes.

This is where XLCubed’s ActivateXL3Link and XL3RefreshObjects comes into play.

Let’s look at the XL3ActivateLink first.  As you can see from our slicer screenshot above, our slicer is set to activate the XL3Link statement in cell I7.

I7 is set as =XL3Link(XL3Address($K$7),”Set Refresh”,,XL3Address($I$6),TRUE)

As you can see from the screenshot below it sets the target cell I6 to TRUE.

Cell J6 contains the formula =XL3RefreshSheetObjects(I6, “Sheet1”, TRUE).

As I6 is set to TRUE this forces a refresh of all objects in the sheet when the slicer choice changes.

It’s that simple – so the next time your Sales Team want their sales figures displayed as units but the CEO wants to see them expressed as millions impress them with this method!

2 Replies to “How’d you like that….displayed!”

  1. I’d love to use this feature. But I don’t get what you’re doing with cells I6 and so forth. Could you post the a picture of the complete excel range used? In the beginning your explanation is marvellous, but later I’m just getting confused.

    1. Hi Christian,

      That part of the article is describing a ripple effect to force the grid to refresh: the slicer activates an XL3Link, which updates a cell to TRUE, which triggers an XL3RefreshSheetObjects formula.

      You can insert an XL3Link from the XLCubed ribbon > Insert formula > XL3Link. In this scenario, you could leave the “Link to” field blank (we don’t need to jump to a new location in the workbook). The important part is setting the target cell with the value TRUE. You will then need to create an XL3RefreshObjects formula in another cell. This formula needs a ‘trigger’, i.e. something to tell it when to refresh. In this example it is the cell $I$6. So, when the XL3Link it clicked (or activated via the slicer), this sets I6 to true, which tells the XL3RefreshObjects to run. It does this, then sets I6 back to false.

      If you need more details or a working example of this, please contact support@xlcubed.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.